Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
micsafdas
Frequent Visitor

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 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!

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

9 REPLIES 9
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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

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




Feel free to connect with me:
LinkedIn

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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"



Feel free to connect with me:
LinkedIn

AlB
Super User
Super User

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 

SU18_powerbi_badge

micsafdas
Frequent Visitor

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

Mariusz
Community Champion
Community Champion

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

 

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. 😉

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors