cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lcuellar75 Frequent Visitor
Frequent Visitor

Need help extracting data from stacked column with no pattern

Hello,

I am 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!
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Need help extracting data from stacked column with no pattern

Hi,

 

You may refer my solution workbook here.

 

Hope this helps.

 

Untitled.png

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Need help extracting data from stacked column with no pattern

Hi,

 

Does the invoice always have "A" at the beginning?  Is the SKU always a length of 5?  Share patterns, if any, for the invoice number and SKU.

Highlighted
Lcuellar75 Frequent Visitor
Frequent Visitor

Re: Need help extracting data from stacked column with no pattern

Thanks for responding! The Invoice is always 6 characters: a letter followed by 5 numbers (V70587, A30785). The SKU is always 8 digits, no letters. Here’s some of the actual data:

INVOICE:V68758
SKU
63050150
64050008
PRICE/
10.99
17.99
INVOICE:V70518
SKU
25160855
33901225
33601100
25160020
25160551
PRICE/
11.99
2.79
3.99
7.99
6.59

Let me know if you have more questions.
Super User
Super User

Re: Need help extracting data from stacked column with no pattern

Hi,

 

You may refer my solution workbook here.

 

Hope this helps.

 

Untitled.png

View solution in original post

Lcuellar75 Frequent Visitor
Frequent Visitor

Re: Need help extracting data from stacked column with no pattern

Thank you so much, this is very helpful!

Super User
Super User

Re: Need help extracting data from stacked column with no pattern

Hi,

 

If my solution helped, please "Accept it as a solution" and give a Kudos.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 268 members 2,844 guests
Please welcome our newest community members: