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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Lcuellar75
New Member

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

Hi,

 

You may refer my solution workbook here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

 

You may refer my solution workbook here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you so much, this is very helpful!

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.