Forum Discussion
Solved
Pivot / Unpivot?
Hi. I got this table below and i want to convert it to the table underneath it. I'm assuming to use Pivot/unpivot but can't figure out how. Thanks for your input! PM Pro_Key Inv_No Adj_Due ...
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], tm = List.TransformMany( Table.ToList(Source, (x) => x), (x) => List.Split(List.Skip(x, 3), 2), (x, y) => List.FirstN(x, 3) & y ), result = Table.FromList(tm, (x) => x) in result
Hello J_o_n_a_s ,
You can implement this way. ฤฐf it is suitable for you.
I created a dummy data and shared m code you can implement easily.
Table1:
'
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1W0lEKKMrPSk0uMQQyDQ1ApIGhvoG5vpGBkSlIyMICTIMELWCCRgYGBkqxOkAz8jPyEEYYgY0wgqo2Q1JtYYBurqmBEdCIWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PM = _t, Pro_Key = _t, Inv_No = _t, #"Adj_Due _Date1" = _t, Adj_Sum1 = _t, #"Adj_Due _Date2" = _t, Adj_Sum2 = _t]),
#"Changed column type" = Table.TransformColumnTypes(Source, {{"PM", type text}, {"Pro_Key", type text}, {"Inv_No", Int64.Type}, {"Adj_Due _Date1", type date}, {"Adj_Sum1", Int64.Type}, {"Adj_Due _Date2", type date}, {"Adj_Sum2", Int64.Type}}),
#"Removed other columns" = Table.SelectColumns(#"Changed column type", {"PM", "Pro_Key", "Inv_No", "Adj_Due _Date1", "Adj_Sum1"}),
#"Renamed columns" = Table.RenameColumns(#"Removed other columns", {{"Adj_Due _Date1", "Adj_Due _Date"}, {"Adj_Sum1", "Adj_Sum"}})
in
#"Renamed columns"
'
Table2:
'
Table2:
'
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1W0lEKKMrPSk0uMQQyDQ1ApIGhvoG5vpGBkSlIyMICTIMELWCCRgYGBkqxOkAz8jPyEEYYgY0wgqo2Q1JtYYBurqmBEdCIWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PM = _t, Pro_Key = _t, Inv_No = _t, #"Adj_Due _Date1" = _t, Adj_Sum1 = _t, #"Adj_Due _Date2" = _t, Adj_Sum2 = _t]),
#"Changed column type" = Table.TransformColumnTypes(Source, {{"PM", type text}, {"Pro_Key", type text}, {"Inv_No", Int64.Type}, {"Adj_Due _Date1", type date}, {"Adj_Sum1", Int64.Type}, {"Adj_Due _Date2", type date}, {"Adj_Sum2", Int64.Type}}),
#"Removed other columns" = Table.SelectColumns(#"Changed column type", {"PM", "Pro_Key", "Inv_No", "Adj_Due _Date2", "Adj_Sum2"}),
#"Renamed columns" = Table.RenameColumns(#"Removed other columns", {{"Adj_Due _Date2", "Adj_Due _Date"}, {"Adj_Sum2", "Adj_Sum"}})
in
#"Renamed columns"
'
You can disable enable load.
You can disable enable load.
Then, you can click append queries as new.
Append table:
'
let
Source = Table.Combine({Table, #"Table (2)"})
in
Source
'