Copy column name to each row of a table

Bibi13

New Member
Joined
Oct 9, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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 name35 - 2020Week
Product 1
10​
35 - 2020​
Product 2
12​
35 - 2020​
Product 3
5​
35 - 2020​
Product 4
15​
35 - 2020​
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
Product name35 - 2020Product name35 - 2020Week
Product 110Product 11035 - 2020
Product 212Product 21235 - 2020
Product 35Product 3535 - 2020
Product 415Product 41535 - 2020

Power Query:
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
Power Query:
   Ren = Table.RenameColumns(USC,{{"Attribute", "Week"}, {"Value", "Price"}})
   Reorder = Table.ReorderColumns(Ren,{"Product name", "Price", "Week"})
 
Last edited:

Bibi13

New Member
Joined
Oct 9, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
so your example is not representative
post example with 3 or 4 tables (use XL2BB to do that)
 

Bibi13

New Member
Joined
Oct 9, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
representative example means few tables with different headers
anyway try
Product name35 - 2020Product namePriceWeek
Product 110Product 11035 - 2020
Product 212Product 21235 - 2020
Product 35Product 3535 - 2020
Product 415Product 41535 - 2020
Product 51036 - 2020
Product 61236 - 2020
Product name36 - 2020Product 7536 - 2020
Product 510Product 81536 - 2020
Product 612Product 91037 - 2020
Product 75Product 101237 - 2020
Product 815Product 11537 - 2020
Product 121537 - 2020
Product 131038 - 2020
Product name37 - 2020Product 141238 - 2020
Product 910Product 15538 - 2020
Product 1012Product 161538 - 2020
Product 115
Product 1215
Product name38 - 2020
Product 1310
Product 1412
Product 155
Product 1615

Power Query:
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
 
Last edited:

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,316
Messages
5,641,484
Members
417,211
Latest member
loadius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top