Copy column name to each row of a table

[Bibi13]

I have a list of products with weekly prices. I want Power Query to generate a new column with the week number in each row. That means that the name of column 2 has to be in each line of my table, as in my example underneath. This would allow me to include several files with a long list of products and prices, and compare the price for each product over a period of time. Each file has the prices for only one week.

Can anyone help me with this?

Example:

Product name	35 - 2020	Week
Product 1	10​	35 - 2020​
Product 2	12​	35 - 2020​
Product 3	5​	35 - 2020​
Product 4	15​	35 - 2020​

 

[sandy666]

Product name	35 - 2020		Product name	35 - 2020	Week
Product 1	10		Product 1	10	35 - 2020
Product 2	12		Product 2	12	35 - 2020
Product 3	5		Product 3	5	35 - 2020
Product 4	15		Product 4	15	35 - 2020

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    USC = Table.Unpivot(Source, {"35 - 2020"}, "Attribute", "Value"),
    Ren = Table.RenameColumns(USC,{{"Attribute", "Week"}, {"Value", "35 - 2020"}}),
    Reorder = Table.ReorderColumns(Ren,{"Product name", "35 - 2020", "Week"})
in
    Reorder

imho, if you want to join tables I'd change column 35 - 2020 to Price

   Ren = Table.RenameColumns(USC,{{"Attribute", "Week"}, {"Value", "Price"}})
   Reorder = Table.ReorderColumns(Ren,{"Product name", "Price", "Week"})

 

[Bibi13]

I have one file for each week and I want them all imported in Power Query automatically. This means that I only have "35-2020" in week 35 og this year. I see that you use "35 - 2020" in the formula. Does this mean that I have to write a line like that for each week of the year, meaning for each file that I would import in the query? This would then be very inefficient, and that is what I am trying to avoid.

 

[sandy666]

so your example is not representative
post example with 3 or 4 tables (use XL2BB to do that)

 

[Bibi13]

Yes it is representative, very simplified though. The idea is that there are over 400 products with product name in the first column and price in some other column. There are many columns aswell. This means that for each of the 400 products I will have 52 prices each year, meaning 52 files and tables that look exactly like the one in my example. The only thing that is different in each file, is the column name - 1 - 2020 for week 1, 2 - 2020 for week 2 a.o.

 

[sandy666]

representative example means few tables with different headers
anyway try

Product name	35 - 2020		Product name	Price	Week
Product 1	10		Product 1	10	35 - 2020
Product 2	12		Product 2	12	35 - 2020
Product 3	5		Product 3	5	35 - 2020
Product 4	15		Product 4	15	35 - 2020
			Product 5	10	36 - 2020
			Product 6	12	36 - 2020
Product name	36 - 2020		Product 7	5	36 - 2020
Product 5	10		Product 8	15	36 - 2020
Product 6	12		Product 9	10	37 - 2020
Product 7	5		Product 10	12	37 - 2020
Product 8	15		Product 11	5	37 - 2020
			Product 12	15	37 - 2020
			Product 13	10	38 - 2020
Product name	37 - 2020		Product 14	12	38 - 2020
Product 9	10		Product 15	5	38 - 2020
Product 10	12		Product 16	15	38 - 2020
Product 11	5
Product 12	15
Product name	38 - 2020
Product 13	10
Product 14	12
Product 15	5
Product 16	15

let
    Source = Excel.CurrentWorkbook(),
    Filter = Table.SelectRows(Source, each Text.StartsWith([Name], "Table")),
    Expand = Table.ExpandTableColumn(Filter, "Content", {"Product name", "35 - 2020", "36 - 2020", "37 - 2020", "38 - 2020"}, {"Product name", "35 - 2020", "36 - 2020", "37 - 2020", "38 - 2020"}),
    RC = Table.RemoveColumns(Expand,{"Name"}),
    UOC = Table.UnpivotOtherColumns(RC, {"Product name"}, "Week", "Price"),
    TSC = Table.SelectColumns(UOC,{"Product name", "Price", "Week"})
in
    TSC

 

[shaowu459]

As Sandy has mentioned, please show representative example you described in post #5, the right and efficient code may depend on how the source data were organised.