CODEWITHSUNDEEP
powerbipowerquery
nhật thanh Mã
nhật thanh Mã

Reputation: 1

Power Query Solution to transform table

enter image description here

I have 2 tables following the picture, table 1 is from pdf file and I want to use power query to change to table 2, if power query can't use, any library in Python could do that.

Update

enter image description here

To clarify, I mean in table 1, one row will be the name of the column, one row is value, and it's interleave, and any way to transform it to table 2.

Upvotes: 0

Views: 40

Answers (1)

horseyride
horseyride

Reputation: 21373

In powerquery, you can do this (your revised request)

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
AnchorColumn=List.Last(Table.ColumnNames(Source)),
a = Table.RemoveColumns(Table.UnpivotOtherColumns(Table.AlternateRows(Source,1,1,1), {AnchorColumn}, "Attribute", "Value"),{"Attribute"}),
b = Table.RemoveColumns(Table.UnpivotOtherColumns(Table.AlternateRows(Source,0,1,1), {AnchorColumn}, "Attribute", "Value"),{"Attribute"}),
combined = Table.FromColumns(Table.ToColumns(a)&{b[Value]}),
#"Pivoted Column" = Table.Pivot(combined, List.Distinct(combined[Column2]), "Column2", "Column3")
in  #"Pivoted Column"

enter image description here

Upvotes: 1

Related Questions