Forum Discussion

micsafdas's avatar
micsafdas
Frequent Visitor
6 years ago
Solved

Shift every other Rows to new columns

Hello, I have a gruesome Excel in front of me, with which I have 3 main issues.   1. New data for each month is being written in it horizontally. So, data for the next month will be found in new c...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

Thanks 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

 

Smauro's avatar
Smauro
Solution Sage
6 years ago

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"