Cleaning and ordering data from a quotation. Order by levels

[jddaa]

Hi all!

I would like to hear from you about an workflow wich I have to build.  This is an example imput data:

Code		Resumen	Units	Unit Price	Total
01	CHAPTER	CHAPTER DESCRIPTION			5.25
01.01	SUB-CHAPTER	SUB-CHAPTER DESCRIPTION			5.25
GXX1	Product1	ProductDescription1	1.000	0.50	0.50
GXX2	Product2	ProductDescription2	2.000	2.00	4.00
GXX3	Product3	ProductDescription3	1.000	0.25	0.25
GXX4	Product4	ProductDescription4	5.000	0.10	0.50

I would like to organize the products to stay into it subchapter (it can be up to 5 levels, for example 01.02.03.01.01) in order to present them in a matrix table in power bi. I tried first to extract the digits of the code in order to organize it in 5 levels. The point is that each product has an assigned code of the subchapter , summarizing it to the total column. 

Any idea about how to prepare the data to get processed? Any idea will be appreciated, thank you all!!

Total number of rows to process is about 300...

[edugoncalves · Accepted solution]

Hi @jddaa ,

In the original data, do you have any attributes that allows you to know the hierarchy path from "Chapter" to "Product"?

How do you know, without viewing the table, which chapter, sub-chapter a given product belongs to?

You will need to have the hierarchy exposed to the last level (in this case, product)...

For instance, if we have a code like 0101GXX1, we can start creating a star-schema around this:
- Chapter: 01 (first 2 digits);
- Sub-Chapter: 0101 (first 4 digits);

- Product: GXX1.

Best Regards,
Eduardo