Forum Discussion

gdlrsilv3's avatar
gdlrsilv3
Regular Visitor
4 years ago
Solved

question about complicated data transformation

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

InvoiceNumitem_codeqty
1Pack-118031
20180340

 

Item table

item_codeDescriptionitem_type
Pack-11803Eight Pack Orange Juice 355mlMultipack
01803Orange Juice 355mlSingle
00010carton boxpurchased

 

 

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.

 

ParentItemqtyChildItem
Pack-11803801803
Pack-11803100010

 

 

 

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

InvoiceNumitem_codeqty
1018038
20180340

 

 

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 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    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!

     

     

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

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

    • gdlrsilv3's avatar
      gdlrsilv3
      Regular Visitor
      4 years ago

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

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        4 years ago

        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!