Forum Discussion

Braunnz's avatar
Braunnz
Frequent Visitor
3 years ago

Help with a complex transposition of print-formatted data

Hello, I've got a data export that comes in a very unfortunate format as if it were to be printed (there is no alternative to this, much to my dismay). I currently deal with it using an excel macr...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Braunnz ,

 

//A different idea I have was if I could split the query into multiple new tables for smaller/simpler pivots and then consolidate, but I'm not sure how to dynamically split into as many pieces as there are occurrences of value "X".

 

My idea is the same, but I prefer to handle this step in the data source.

1. Format these areas as tables in excel.(CTRL+T)

2. Unpivot columns:

3. append queries as new

4. pivot column and rename some colum:

let
    Source = Table.Combine({Table1, Table2, Table3, Table4}),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"Location #"]), "Location #", "Value", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Location #"}})
in
    #"Renamed Columns"

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

  • Braunnz's avatar
    Braunnz
    Frequent Visitor
    3 years ago

    Hi Gao,

    Thanks for the reply. Handling at the source is what I'm currently doing via VBA. My goal is to fully remove that manual step with Power Query. Would you say that it's more trouble than it's worth?

    I was able to figure it out in Excel because commands like selecting a range of rows based on values and cut/paste after the last column are great for the rearranging as needed, but I don't have a clue about translating those operations into the M lanaguage.

    Cheers,
    Braunnz