Forum Discussion
Shift every other Rows to new columns
You're right. That is some messy data. Please see if this M code gets your desired result from your sample data. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnIN8A8KUXBxDHFVUNJR8k0s0lMwMgCysCLHgiJd3LK+iZXYZWN1orHrIBmNmjTkTXINDQKSocEuQNLZJwAi6ZNalpqjYAhnGQFZQam5iUXZxRAFdNJFXd86J5aA7TFAwmgIJKRraoAk65yfm5uaB9EIYxuhqMc0CmaZEcxEQwNijIT4GskhRkRpI8YlxoS9jS9EjLE6BGZ5bCwA", 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, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Column2] <> "")),
#"Transposed Table" = Table.Transpose(#"Filtered Rows"),
#"Changed Type" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column1"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"Column1", "Column2"}, "Attribute", "Value"),
#"Filtered Rows3" = Table.SelectRows(#"Unpivoted Other Columns", each ([Column1] = "REPORT DATE ")),
CatList = #"Filtered Rows3"[Value],
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Column1] <> "REPORT DATE ")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows1", "Category", 1, 1),
#"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Category", each Number.Mod(_-1, 3)+1, type number}}),
#"Filtered Rows2" = Table.SelectRows(#"Calculated Modulo", each ([Column2] <> "")),
#"Added Prefix" = Table.TransformColumns(#"Filtered Rows2", {{"Category", each CatList{_-1}, type text}}),
#"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Column2]), "Column2", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Column1", type text}, {"Category", type text}, {"EUR", Int64.Type}, {"USD", Int64.Type}, {"CLP", Int64.Type}, {"Level 1", type text}, {"Level 2", type text}, {"Remarks", type text}})
in
#"Changed Type1"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
You're right. That is some messy data. Please see if this M code gets your desired result from your sample data. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnIN8A8KUXBxDHFVUNJR8k0s0lMwMgCysCLHgiJd3LK+iZXYZWN1orHrIBmNmjTkTXINDQKSocEuQNLZJwAi6ZNalpqjYAhnGQFZQam5iUXZxRAFdNJFXd86J5aA7TFAwmgIJKRraoAk65yfm5uaB9EIYxuhqMc0CmaZEcxEQwNijIT4GskhRkRpI8YlxoS9jS9EjLE6BGZ5bCwA", 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, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Column2] <> "")),
#"Transposed Table" = Table.Transpose(#"Filtered Rows"),
#"Changed Type" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column1"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"Column1", "Column2"}, "Attribute", "Value"),
#"Filtered Rows3" = Table.SelectRows(#"Unpivoted Other Columns", each ([Column1] = "REPORT DATE ")),
CatList = #"Filtered Rows3"[Value],
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Column1] <> "REPORT DATE ")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows1", "Category", 1, 1),
#"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Category", each Number.Mod(_-1, 3)+1, type number}}),
#"Filtered Rows2" = Table.SelectRows(#"Calculated Modulo", each ([Column2] <> "")),
#"Added Prefix" = Table.TransformColumns(#"Filtered Rows2", {{"Category", each CatList{_-1}, type text}}),
#"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Column2]), "Column2", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Column1", type text}, {"Category", type text}, {"EUR", Int64.Type}, {"USD", Int64.Type}, {"CLP", Int64.Type}, {"Level 1", type text}, {"Level 2", type text}, {"Remarks", type text}})
in
#"Changed Type1"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi mahoneypat,
I was reading your solution, and it looks great. One thing I wanted to point out was that the Category count may change, so it would be better to use#"Calculated Modulo" = let n = List.Count(CatList) in Table.TransformColumns(#"Added Index", {{"Category", each Number.Mod(_-1, n)+1, type number}}),instead.
CheersThanks for the fix, Smauro . I meant to make it dynamic, but forgot to go back and do that step. I always enjoy solutions that make use of List functions.
Regards,
Pat
I trimmed the code down a little bit
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnIN8A8KUXBxDHFVUNJR8k0s0lMwMgCysCLHgiJd3LK+iZXYZWN1orHrIBmNmjTkTXINDQKSocEuQNLZJwAi6ZNalpqjYAhnGQFZQam5iUXZxRAFdNJFXd86J5aA7TFAwmgIJKRraoAk65yfm5uaB9EIYxuhqMc0CmaZEcxEQwNijIT4GskhRkRpI8YlxoS9jS9EjLE6BGZ5bCwA", 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, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t]), #"Filtered Rows" = Table.SelectRows(Source, each ([Column2] <> "")), #"Transposed Table" = Table.Transpose(#"Filtered Rows"), #"Replaced Value" = Table.ReplaceValue(#"Transposed Table","",null,Replacer.ReplaceValue,{"Column1"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}), #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]), #"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([#""] <> "")), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows1", {"REPORT DATE ", ""}, "Item", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[#""]), "", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"REPORT DATE ", type date}, {"EUR", Int64.Type}, {"USD", Int64.Type}, {"CLP", Int64.Type}, {"Level 1", type text}, {"Level 2", type text}, {"Remarks", type text}}) in #"Changed Type"
That completely did the trick. Thanks, very impressive. 🙂
Generally, you guys are awesome, thanks to all of you!
