question about complicated data transformation

[gdlrsilv3]

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 

[AlexisOlson]

It looks like you are ignoring the second ChildItem, 00010. Why do you pick 8 instead of 1 or 8+1?

[gdlrsilv3]

I won't consider item 00010 because it is a carton, only items with item_type = 'Single' will be considered.

[v-kelly-msft · Accepted 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!