excelpowerquerycustomcolumn

Power Query: How to add column with repeating values

I have an excel table with the fields ProductCode, ProductDescription and Sales. I would like to add a column named "Store", where the values come from certain header rows, as seen in the following images.

Thanks!

Original:

enter image description here

Desired result:

enter image description here

Sample File

Solution

  • This would extract the store and put it in another column

    Test in Source line is the name of the table

    let
    Source = Excel.CurrentWorkbook(){[Name="Test"]}[Content],
    ChangeTypes = Table.TransformColumnTypes(Source,{{"ProductCode", type text}, {"ProductDescription", type text}, {"Sales", Int64.Type}}),
    AddStoreCol = Table.AddColumn(ChangeTypes, "Store", each if [ProductDescription] = null and Text.Start([ProductCode],5) <> "TOTAL" then [ProductCode] else null, type text),
    FillDown = Table.FillDown(AddStoreCol,{"Store"}),
    FilterNulls = Table.SelectRows(FillDown, each [ProductDescription] <> null and [ProductDescription] <> "")
in
    FilterNulls

    Let me know if it works