Forum Discussion
Solved
Data Transformation unpivoting for multiple headers
Hi All, I have my data set in below formation where every week column will increase based on dates. I want to transpose all columns to row level , can anybody assist me how to build power query so t...
Whoever created that input format should be led behind the shed for a friendly discussion with a clue-by-four.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSgeOAABxEaDhuMlYnWskjNTEltUjBECgEZRohmMZApqGRroGFrpGBkQmIY4zMMUHmmCJzzJA5lBkAcqRPfnJiSWZ+HtiZjk5gx3qASJBakHtNocFgjMQGYUMwjWKEEdgIsCc9jKDKjKBGwbSaQrUaw41BMcIYbIQx2AhjqDJzJJcYQmkzILaAGmGmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]), Table = Table.AddIndexColumn(Table.PromoteHeaders(Table.Skip(Table.SelectColumns(Source,{"Column1", "Column2", "Column3"}),1), [PromoteAllScalars=true]), "Index", 0, 1, Int64.Type), #"Removed Columns" = Table.RemoveColumns(Source,{"Column1", "Column2", "Column3"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", -2, 1, Int64.Type), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Date", (k)=> Table.SelectRows(#"Unpivoted Other Columns",each [Attribute]=k[Attribute] and [Index]=-1)[Value]{0}, type date), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Category", (k)=> Table.SelectRows(#"Unpivoted Other Columns",each [Attribute]=k[Attribute] and [Index]=-2)[Value]{0},type text), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Index] <> -2 and [Index] <> -1)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Category]), "Category", "Value"), #"Merged Queries" = Table.NestedJoin(Table, {"Index"},#"Pivoted Column", {"Index"}, "Table", JoinKind.Inner), #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Date", "PP", "UWP"}, {"Date", "PP", "UWP"}), #"Removed Columns2" = Table.RemoveColumns(#"Expanded Table",{"Index"}) in #"Removed Columns2"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
borrowed Source from lbendlin's post...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSgeOAABxEaDhuMlYnWskjNTEltUjBECgEZRohmMZApqGRroGFrpGBkQmIY4zMMUHmmCJzzJA5lBkAcqRPfnJiSWZ+HtiZjk5gx3qASJBakHtNocFgjMQGYUMwjWKEEdgIsCc9jKDKjKBGwbSaQrUaw41BMcIYbIQx2AhjqDJzJJcYQmkzILaAGmGmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]),
data_col_no = (Table.ColumnCount(Source) - 3) / 2,
dates = List.Buffer(List.Split(List.Skip(Record.FieldValues(Source{1}), 3), data_col_no){0}),
to_list = Table.ToList(
Table.Skip(Source, 2),
(w) => List.TransformMany(
{w},
(x) => List.Zip({dates} & List.Split(List.Skip(x, 3), data_col_no)),
(x, y) => List.FirstN(x, 3) & y
)
),
to_tbl = Table.FromList(List.Combine(to_list), (x) => x, {"Header 1", "Header 2", "Header 3", "Date", "PP", "UWP"})
in
to_tbl
