Solved Transform Data with Multiple Rows and Multiple Columns using Power Query

[sameer79]

Hello All,
I am enclosing a sample data.
Can it be transformed using power query.

Sheet1 is the Input
Sheet2 is the Output desired

TIA

 

[p45cal]

In haste, a really ugly Power Query M-code in the attached resulting in table at cell J1 of Sheet2, which is exactly the same as your expected table.

 

[sameer79]

Hello there,
Thanks for your prompt reply and sorry for my late response.
I cannot test it as in the Applied Steps on Step6 spw2 I get the following error shown in the attached file.

 

[p45cal]

Update your version of Excel or change:
#"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1, Int64.Type)
to:
#"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1)

The same will apply to step Added Index (step 15 or so) of Result query:
= Table.AddIndexColumn(#"Removed Columns2", "Serial Number", 1, 1, Int64.Type)
becomes:
= Table.AddIndexColumn(#"Removed Columns2", "Serial Number", 1, 1)

 

[sameer79]

Thanks
I will check and revert

 

[sameer79]

Thanks
I will check and revert

Thank you so much for your time and help.