Help with a complex transposition of print-formatted data

[Braunnz]

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 macro but would like to fully migrate the work to PBI. The data comes like this:

At the end of the macro it looks like this:

What should be new location columns get pushed down to into the rows beneath to keep margins consistent. There's also a limit to how many products per 'chunk' it shows before starting a new group back at location 1. There can also be blank fields if the total amount of columns doesn't divide evenly into the number of chunks. (Note the actual data count of products and locations is around 60 for both).

"Location#" in column 1 marks where each page begins, and "Location1" in column 2 marks where a new chunk begins.

Some intial searching led me to the pivot column function, but playing around with it, it doesn't seem suited for this particular structure.

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

Thanks in advance for any insight.

Here is the tabular version of the data if it's helpful:

Location #	Location 1	Location 2	Location 3	Location 4
Product 1	5	9	9	1
Product 2	8	7	5	2
Product 3	4	5	2	3
Location #	Location 5	Location 6
Product 1	3	7
Product 2	4	5
Product 3	4	2
Location #	Location 1	Location 2	Location 3	Location 4
Product 4	0	2	7	5
Product 5	4	3	6	9
Location #	Location 5	Location 6
Product 4	7	8
Product 5	9	1

[v-cgao-msft]

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]

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