Pivot rows as Columns

[sa34techer]

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

[NaveenGandhi]

Hi @sa34techer 

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!!!

[sa34techer]

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

[NaveenGandhi · Accepted solution]

@sa34techer 

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!!!