Forum Discussion
Cleaning and ordering data from a quotation. Order by levels
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...
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
1 Reply
- edugoncalves
Resolver I
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