Shift every other Rows to new columns

[micsafdas]

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 columns, instead of rows. This follows a schema. So I would need to shift/move these new rows benath the original data.

2. The explaination column is only available once in column A. So it would need to be copied down before the new data is being moved.

3. The data info is in a cell above the header row. That would need to be incorporated as well.

You see, it's bad. Do you have an idea, or is this not possible?

Sample Data: https://drive.google.com/file/d/1sgdcQkn_YOQwxxp4A1qtjI2HCyDd81Yj/view?usp=sharing

Thanks!

[mahoneypat · Accepted solution]

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

[micsafdas]

That completely did the trick. Thanks, very impressive. 🙂

Generally, you guys are awesome, thanks to all of you!

[Smauro]

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.

Cheers

[mahoneypat]

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]

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"

[AlB]

Hi @micsafdas 

the file cannot be accessed. You probably have to activate the option "Anyone with the link can access" or something similar

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[micsafdas]

You're right. Should be fixed now. Thanks for pointing that out.

[Mariusz]

Hi @micsafdas 

The best way to keep historic data would be by saving the file on the last day and later merging all files together, for the month's columns you can use Unpivot Other Columns ( select all columns excluding months to right-click any of the column headers and select Unpivot Other Columns from the dropdown ).

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[micsafdas]

Thanks for your answer. But this wouldn't be a one time thing. I expect at least 100 of this files, all being updated every month and there is no acceptance of having a separate source for historic data nor to any changes in the excel. You gotta love big corps. 😉