Forum Discussion
Pivot rows as Columns
I get header dates in my excel extract on the first 4 rows. Please help me pivot the row names and dates as columns into the bottom table using power query. I have included a sample and data.
| Property Name | Export Dates | ||||||||||||||
| Period From | 9-Aug-24 | ||||||||||||||
| Period Upto | 19-Aug-24 | ||||||||||||||
| Exported On | 19-Aug-24 | ||||||||||||||
| BIG Source | Total Volume | Rating | Previous Rating | UP/Down | Promote | Food | Bunny | GPS | Single Rating | Previous Single Rating | HFT | Yoggy | Bed | Grond | BP |
| Duck | 324 | 3.88 | 4.04 | 444 | 31 | 157 | 45.41 | 3.87 | 4.04 | 335 | 14 | 125 | 44.3 |
Anonymous
Below m code will work for this requirement.let Source = Excel.Workbook(File.Contents("C:\Users\Violent Gandhi\Downloads\Test.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], Table_1_filtered = Table.SelectRows(Sheet1_Sheet, each ([Column3] = null) and ([Column2] <> null)), RemovedOtherColumns = Table.SelectColumns(Table_1_filtered,{"Column1", "Column2"}), PromotedHeaders = Table.PromoteHeaders(RemovedOtherColumns, [PromoteAllScalars=true]), TransposedTable = Table.Transpose(PromotedHeaders), PromotedHeaders1 = Table.PromoteHeaders(TransposedTable, [PromoteAllScalars=true]), Table_2_filtered = Table.SelectRows(Sheet1_Sheet, each ([Column3] <> null)), PromotedHeaders3 = Table.PromoteHeaders(Table_2_filtered, [PromoteAllScalars=true]), Addcolumn = Table.AddColumn(PromotedHeaders3,"Exported On",each PromotedHeaders1[Exported On]{0}), JoinedTables = Table.Join(Addcolumn,"Exported On",PromotedHeaders1,"Exported On",JoinKind.Inner) in JoinedTables
Attached PBIX has a table named single sheet for your reference.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
Follow me on LinkedIn!!!
3 Replies
Hi Anonymous
Attached PBIX has solution for this.
Let me know if this helps.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
Follow me on LinkedIn!!!Both tables come into the same excel sheet. I need them combined at the final step in power query.
This what the final output should be.
Period From Period Upto Exported On Total Volume Rating Previous Rating UP/Down Promote Food Bunny GPS Single Rating Previous Single Rating HFT Yoggy Bed Grond BP 9-Aug-24 19-Aug-24 19-Aug-24 324 3.88 4.04 444 31 157 45.41 3.87 4.04 335 14 125 44.3 Anonymous
Below m code will work for this requirement.let Source = Excel.Workbook(File.Contents("C:\Users\Violent Gandhi\Downloads\Test.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], Table_1_filtered = Table.SelectRows(Sheet1_Sheet, each ([Column3] = null) and ([Column2] <> null)), RemovedOtherColumns = Table.SelectColumns(Table_1_filtered,{"Column1", "Column2"}), PromotedHeaders = Table.PromoteHeaders(RemovedOtherColumns, [PromoteAllScalars=true]), TransposedTable = Table.Transpose(PromotedHeaders), PromotedHeaders1 = Table.PromoteHeaders(TransposedTable, [PromoteAllScalars=true]), Table_2_filtered = Table.SelectRows(Sheet1_Sheet, each ([Column3] <> null)), PromotedHeaders3 = Table.PromoteHeaders(Table_2_filtered, [PromoteAllScalars=true]), Addcolumn = Table.AddColumn(PromotedHeaders3,"Exported On",each PromotedHeaders1[Exported On]{0}), JoinedTables = Table.Join(Addcolumn,"Exported On",PromotedHeaders1,"Exported On",JoinKind.Inner) in JoinedTables
Attached PBIX has a table named single sheet for your reference.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
Follow me on LinkedIn!!!