Pulling Data from Stacked Column with No Pattern

lcuellar75
Posts: 1
Joined: Sun Jul 29, 2018 9:39 am

Pulling Data from Stacked Column with No Pattern

Postby lcuellar75 » Sun Jul 29, 2018 9:48 am

Hello,

I having a very hard time trying to extract data from a Tabula export that stacks my data in one column. I have seen several methods using a modulo column, but they all require the data to be stacked in a pattern. My data is stacked like this:

Invoice
A11111
SKU
55555
55556
55557
Price
2.25
3.25
4.25
Invoice
A22222
SKU
66669
66668
66667
66666
66665
Price
1.25
1.75
3.25
6.25
6.25

Where the prices correspond the product above them. I want to get a table with an Invoice, SKU and a Price column. Product 55555 has a price of 2.25 and product 66669 has a price of 1.25, etc Please let me know if you have a trick for this!
Attachments
Product Export.xlsx
Here is the data I'm working with
(88.39 KiB) Downloaded 32 times

MattAllington
Posts: 989
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Pulling Data from Stacked Column with No Pattern

Postby MattAllington » Tue Jul 31, 2018 7:32 am

FYI, I created a video showing how to do this yesterday. I hope to share it today.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

MattAllington
Posts: 989
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Pulling Data from Stacked Column with No Pattern

Postby MattAllington » Tue Jul 31, 2018 3:22 pm

OK, here is my YouTube session that explains how I solved this problem. https://www.youtube.com/watch?v=KrJH4elS9eM
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

gnaffle
Posts: 1
Joined: Tue Jul 31, 2018 6:45 pm

Re: Pulling Data from Stacked Column with No Pattern

Postby gnaffle » Tue Jul 31, 2018 6:56 pm

Thanks for the video, really like your pace and thinking approach with the video very easy to follow and learned some really good tricks!

Bill Szysz
Posts: 1
Joined: Fri Aug 17, 2018 8:57 am

Re: Pulling Data from Stacked Column with No Pattern

Postby Bill Szysz » Fri Aug 17, 2018 9:05 am

Hi Matt :-)
I think, this is not as easy as you've shown in your video.
Original file contains many unexpected exceptions.
See the attachment.... this is only dirty query i wrote (ver 0.001 :-)) )

Regards
Bill
Attachments
Product Export_BS_PQ.xlsx
(167.75 KiB) Downloaded 22 times


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 2 guests