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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Braunnz
Frequent Visitor

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

Braunnz_0-1666208456547.png

At the end of the macro it looks like this:

Braunnz_1-1666208506332.png

 

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 1Location 2Location 3Location 4
Product 15991
Product 28752
Product 34523
Location #Location 5Location 6  
Product 137  
Product 245  
Product 342  
Location #Location 1Location 2Location 3Location 4
Product 40275
Product 54369
Location #Location 5Location 6  
Product 478  
Product 591  





2 REPLIES 2
v-cgao-msft
Community Support
Community Support

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)

vcgaomsft_0-1666252045342.png

2. Unpivot columns:

vcgaomsft_1-1666252170632.png

vcgaomsft_2-1666252197803.png

3. append queries as new

vcgaomsft_3-1666252233795.png

vcgaomsft_4-1666252263108.png

4. pivot column and rename some colum:

vcgaomsft_5-1666252354094.png

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.