Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a question on data transformation, I have the following scenario, in my imported invoices information I have part numbers that are multipack (a box with N single products) and also part numbers that are single types when they are sold individually.
Sales table
InvoiceNum | item_code | qty |
1 | Pack-11803 | 1 |
2 | 01803 | 40 |
Item table
item_code | Description | item_type |
Pack-11803 | Eight Pack Orange Juice 355ml | Multipack |
01803 | Orange Juice 355ml | Single |
00010 | carton box | purchased |
also I have a table BOM, that tells how the multipack part number is built, in this case it is telling me that multipack part number (Pack-11803) will have 8 single items part number 01803.
ParentItem | qty | ChildItem |
Pack-11803 | 8 | 01803 |
Pack-11803 | 1 | 00010 |
how could I transform the data to replace the multipack part number with the single item part numbers and the right quantity?. At the end of the transformation I should have:
desired Sales table
InvoiceNum | item_code | qty |
1 | 01803 | 8 |
2 | 01803 | 40 |
any body who could point me to the right direction? I'm new in Power BI
thanks for any help, regards,
here is the link to the PBIX
https://1drv.ms/f/s!AM_4N0ZUNxX-gQs
Solved! Go to Solution.
Hi @gdlrsilv3 ,
First create a column in Bom:
Invoice number = LOOKUPVALUE('Sales'[InvoiceNum],'Sales'[item_code],'BOM'[ParentItem],blank())
And create a table as below:
Union = UNION('Sales',SELECTCOLUMNS('BOM',"InvoiceNum",'BOM'[Invoice number],"item_code",'BOM'[ChildItem],"qty",'BOM'[qty]))
Then create a measure:
Measure =
VAR _type =
CALCULATETABLE (
VALUES ( 'Item'[item_code] ),
FILTER ( ALL ( 'Item' ), 'Item'[item_type] = "Single" )
)
RETURN
IF ( MAX ( 'Union'[item_code] ) IN _type, 1, BLANK () )
Put measure in the filter pane and select measure is 1;
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
It looks like you are ignoring the second ChildItem, 00010. Why do you pick 8 instead of 1 or 8+1?
I won't consider item 00010 because it is a carton, only items with item_type = 'Single' will be considered.
Hi @gdlrsilv3 ,
First create a column in Bom:
Invoice number = LOOKUPVALUE('Sales'[InvoiceNum],'Sales'[item_code],'BOM'[ParentItem],blank())
And create a table as below:
Union = UNION('Sales',SELECTCOLUMNS('BOM',"InvoiceNum",'BOM'[Invoice number],"item_code",'BOM'[ChildItem],"qty",'BOM'[qty]))
Then create a measure:
Measure =
VAR _type =
CALCULATETABLE (
VALUES ( 'Item'[item_code] ),
FILTER ( ALL ( 'Item' ), 'Item'[item_type] = "Single" )
)
RETURN
IF ( MAX ( 'Union'[item_code] ) IN _type, 1, BLANK () )
Put measure in the filter pane and select measure is 1;
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.