Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Data4Beer
Frequent Visitor

Help me manipulate this data

Hi everyone!

 

I am struggling to solve this problem. I have bill of materials which contains anInventoryID's and the materials.

 

In this example, there are 4 Work in Progress phases (inventory items), W1,W2,W3,FG. From W2 onwards, the item makes use of the previous WIP item and eventually produces a finished good. See the first table below:

Question.PNG

 

 

I need to be able to manipulate and add a column that shows what finished good inventoryID is being produced such as the table below:

Result.PNG

 I would appreciate it if someone could assist me.

 

Thanks!

2 ACCEPTED SOLUTIONS

In the query below, recursive function ExplodeBOM is used as part of a solution that is independent from the sort order of the original table.

 

As a prerequisite, all materials must have the same case for their codes, e.g. w1br001 is not the same as W1BR001.

 

This also allows for subassemblies to appear in multiple finished goods.

 

In each iteration, a new BOM level is added to the resulting table.

 

let
    Source = Table1,
    SelectedFinishedGoods = Table.NestedJoin(Source,{"InventoryID"},Table1,{"Material Added"},"Table1",JoinKind.LeftAnti),
    RemovedJoinColumn = Table.RemoveColumns(SelectedFinishedGoods,{"Table1"}),
    AddedFinishedInventoryID = Table.Buffer(Table.DuplicateColumn(RemovedJoinColumn, "InventoryID", "Finished InventoryID")),

    ExplodeBOM = (TableSoFar as table, PreviousTable as table) as table =>
    let
        SelectedRemainingRecords = Table.NestedJoin(PreviousTable,{"InventoryID"},TableSoFar,{"InventoryID"},"JoinColumn",JoinKind.LeftAnti),
        RemainingRecords = Table.RemoveColumns(SelectedRemainingRecords,{"JoinColumn"}),
        SelectedNewRecords = Table.NestedJoin(RemainingRecords,{"InventoryID"},TableSoFar,{"Material Added"},"RemainingRecords",JoinKind.Inner),
        NewRecords = Table.ExpandTableColumn(SelectedNewRecords, "RemainingRecords", {"Finished InventoryID"}),
        NewTable = Table.Buffer(TableSoFar & NewRecords),
        Result = if Table.IsEmpty(SelectedRemainingRecords) then TableSoFar else @ExplodeBOM(NewTable, RemainingRecords)
    in
        Result,

    ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,Source),
    Sorted = Table.Sort(ExplodedBOM,{{"Finished InventoryID", Order.Ascending}, {"Material Added", Order.Ascending}}),
    Reordered = Table.ReorderColumns(Sorted,{"Finished InventoryID", "InventoryID", "Material Added"}) 

in
    Reordered

 

Specializing in Power Query Formula Language (M)

View solution in original post

Troubleshooting your issues takes me a multitude of time that was required to come up with a solution in the first place.

 

Again, your ExplodedBOM step is wrong.

 

It should be:

 

    ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,#"Removed Duplicates"),

 

My suggestion would be not to use any solution you don't understand.

Specializing in Power Query Formula Language (M)

View solution in original post

15 REPLIES 15
Zubair_Muhammad
Community Champion
Community Champion

Hi @Data4Beer

 

Try this

 

let
    Source = Excel.CurrentWorkbook(){[Name="TableName"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"InventoryID", type text}, {"Material Added", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.PositionOf([InventoryID],"FGbr") >= 0 then [InventoryID] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each not Text.Contains([InventoryID], "FGbr")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Custom", "InventoryID", "Material Added"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Finished Inventory ID"}})
in
    #"Renamed Columns"

 

 

 


Regards
Zubair

Please try my custom visuals

@Data4Beer

 

Basically we follow these steps

 

 

Step#1 Add a custom column using this formula

 

=if Text.PositionOf([InventoryID],"FGbr") >= 0 then [InventoryID] else null

 

2001.png

 

 

Step #2  : Select the custom Column>>> Goto "Transform" tab>>FillUp

You will get

 

2002.png

 

 

 

 


Regards
Zubair

Please try my custom visuals

@Data4Beer

 

Step#3 Filter the InventoryID column

"Does not contain FGbr"

 

2003.png

 

 

Step#4: Rename the Custom Column and Reorder it


Regards
Zubair

Please try my custom visuals

Hi Zubair

 

Thank you for your response.

 

My only issue with this method is that the data does not reflect a sequential order as the example does. Therefore the 'Fillup' function is not a solution. Thank you for your time and effort.

In the query below, recursive function ExplodeBOM is used as part of a solution that is independent from the sort order of the original table.

 

As a prerequisite, all materials must have the same case for their codes, e.g. w1br001 is not the same as W1BR001.

 

This also allows for subassemblies to appear in multiple finished goods.

 

In each iteration, a new BOM level is added to the resulting table.

 

let
    Source = Table1,
    SelectedFinishedGoods = Table.NestedJoin(Source,{"InventoryID"},Table1,{"Material Added"},"Table1",JoinKind.LeftAnti),
    RemovedJoinColumn = Table.RemoveColumns(SelectedFinishedGoods,{"Table1"}),
    AddedFinishedInventoryID = Table.Buffer(Table.DuplicateColumn(RemovedJoinColumn, "InventoryID", "Finished InventoryID")),

    ExplodeBOM = (TableSoFar as table, PreviousTable as table) as table =>
    let
        SelectedRemainingRecords = Table.NestedJoin(PreviousTable,{"InventoryID"},TableSoFar,{"InventoryID"},"JoinColumn",JoinKind.LeftAnti),
        RemainingRecords = Table.RemoveColumns(SelectedRemainingRecords,{"JoinColumn"}),
        SelectedNewRecords = Table.NestedJoin(RemainingRecords,{"InventoryID"},TableSoFar,{"Material Added"},"RemainingRecords",JoinKind.Inner),
        NewRecords = Table.ExpandTableColumn(SelectedNewRecords, "RemainingRecords", {"Finished InventoryID"}),
        NewTable = Table.Buffer(TableSoFar & NewRecords),
        Result = if Table.IsEmpty(SelectedRemainingRecords) then TableSoFar else @ExplodeBOM(NewTable, RemainingRecords)
    in
        Result,

    ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,Source),
    Sorted = Table.Sort(ExplodedBOM,{{"Finished InventoryID", Order.Ascending}, {"Material Added", Order.Ascending}}),
    Reordered = Table.ReorderColumns(Sorted,{"Finished InventoryID", "InventoryID", "Material Added"}) 

in
    Reordered

 

Specializing in Power Query Formula Language (M)

Hi Marcel

 

Thank you for your detailed reponse.

 

I have tried the following from the example.

 

I get a 'token Identifier expected' error

 

Where:

InventoryID = InventoryID_2

Material Added = InventoryID

Capture.PNG

 

 

 

Your step name with double quotes, should also have a #, as in:

#"step name"

Specializing in Power Query Formula Language (M)

Now I am getting 'Token Comma Expected'

 

Capture.PNG

You need to provide the step name from the previous step.

Actually you also need to replace "Source" with the step name of the previous step.

 

In my example, Source was the same as Table1, which is - in your case - the name of the #"BI....."step.

(I'm not going to type over from your pictures, so if you need further clarity, please provide code as text, instead of pictures).

Specializing in Power Query Formula Language (M)

let
    Source = OData.Feed("https://example.co.za/odata/example"),
    #"BI - BOM Material_table" = Source{[Name="BI - BOM Material",Signature="table"]}[Data],
    SelectedFinishedGoods = Table.NestedJoin(Source,{"InventoryID_2"},BI - BOM Material_table,{"InventoryID"},"BI - BOM Material_table",JoinKind.LeftAnti),
    RemovedJoinColumn = Table.RemoveColumns(SelectedFinishedGoods,{"BI - BOM Material_table"}),
    AddedFinishedInventoryID = Table.Buffer(Table.DuplicateColumn(RemovedJoinColumn, "InventoryID_2", "Finished InventoryID")),

    ExplodeBOM = (TableSoFar as table, PreviousTable as table) as table =>
    let
        SelectedRemainingRecords = Table.NestedJoin(PreviousTable,{"InventoryID_2"},TableSoFar,{"InventoryID_2"},"JoinColumn",JoinKind.LeftAnti),
        RemainingRecords = Table.RemoveColumns(SelectedRemainingRecords,{"JoinColumn"}),
        SelectedNewRecords = Table.NestedJoin(RemainingRecords,{"InventoryID_2"},TableSoFar,{"InventoryID"},"RemainingRecords",JoinKind.Inner),
        NewRecords = Table.ExpandTableColumn(SelectedNewRecords, "RemainingRecords", {"Finished InventoryID"}),
        NewTable = Table.Buffer(TableSoFar & NewRecords),
        Result = if Table.IsEmpty(SelectedRemainingRecords) then TableSoFar else @ExplodeBOM(NewTable, RemainingRecords)
    in
        Result,

    ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,Source),
    Sorted = Table.Sort(ExplodedBOM,{{"Finished InventoryID", Order.Ascending}, {"InventoryID", Order.Ascending}}),
    Reordered = Table.ReorderColumns(Sorted,{"Finished InventoryID", "InventoryID_2", "InventoryID"}) 
in
    Reordered

Hi Marcel 

 

I would appreciate the clarity. See code attached.

 

Thanks

Your code, with step SelectedFinishedGoods adjusted:

 

let
    Source = OData.Feed("https://example.co.za/odata/example"),
    #"BI - BOM Material_table" = Source{[Name="BI - BOM Material",Signature="table"]}[Data],
    SelectedFinishedGoods = Table.NestedJoin(#"BI - BOM Material_table",{"InventoryID_2"},#"BI - BOM Material_table",{"InventoryID"},"BI - BOM Material_table",JoinKind.LeftAnti),
    RemovedJoinColumn = Table.RemoveColumns(SelectedFinishedGoods,{"BI - BOM Material_table"}),
    AddedFinishedInventoryID = Table.Buffer(Table.DuplicateColumn(RemovedJoinColumn, "InventoryID_2", "Finished InventoryID")),

    ExplodeBOM = (TableSoFar as table, PreviousTable as table) as table =>
    let
        SelectedRemainingRecords = Table.NestedJoin(PreviousTable,{"InventoryID_2"},TableSoFar,{"InventoryID_2"},"JoinColumn",JoinKind.LeftAnti),
        RemainingRecords = Table.RemoveColumns(SelectedRemainingRecords,{"JoinColumn"}),
        SelectedNewRecords = Table.NestedJoin(RemainingRecords,{"InventoryID_2"},TableSoFar,{"InventoryID"},"RemainingRecords",JoinKind.Inner),
        NewRecords = Table.ExpandTableColumn(SelectedNewRecords, "RemainingRecords", {"Finished InventoryID"}),
        NewTable = Table.Buffer(TableSoFar & NewRecords),
        Result = if Table.IsEmpty(SelectedRemainingRecords) then TableSoFar else @ExplodeBOM(NewTable, RemainingRecords)
    in
        Result,

    ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,Source),
    Sorted = Table.Sort(ExplodedBOM,{{"Finished InventoryID", Order.Ascending}, {"InventoryID", Order.Ascending}}),
    Reordered = Table.ReorderColumns(Sorted,{"Finished InventoryID", "InventoryID_2", "InventoryID"}) 
in
    Reordered

 

Specializing in Power Query Formula Language (M)

Also when invoking the recursive function, you must supply #"BI - BOM Material_table" instead of Source:

 

ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,#"BI - BOM Material_table"),
Specializing in Power Query Formula Language (M)

Hi Marcel

 

When I try run the query, powerbi crashes. Please will you review the code attached.

 

Here is a link to the data:

 

https://www.dropbox.com/sh/ypsalwssnhe8nip/AACbNw3JwjWzby1oSBIR83hYa?dl=0

 

let
    Source = OData.Feed("https://example.co.za/odata/example"),
    #"BI - BOM Material_table" = Source{[Name="BI - BOM Material",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(#"BI - BOM Material_table", each Text.StartsWith([Materials], "W") or Text.StartsWith([Materials], "FG")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows"),
    SelectedFinishedGoods = Table.NestedJoin(#"Removed Duplicates",{"InventoryID"},#"Removed Duplicates",{"Materials"},"BI - BOM Material_table",JoinKind.LeftAnti),
    RemovedJoinColumn = Table.RemoveColumns(SelectedFinishedGoods,{"BI - BOM Material_table"}),
    AddedFinishedInventoryID = Table.Buffer(Table.DuplicateColumn(RemovedJoinColumn, "InventoryID", "Finished InventoryID")),

    ExplodeBOM = (TableSoFar as table, PreviousTable as table) as table =>
    let
        SelectedRemainingRecords = Table.NestedJoin(PreviousTable,{"InventoryID"},TableSoFar,{"InventoryID"},"JoinColumn",JoinKind.LeftAnti),
        RemainingRecords = Table.RemoveColumns(SelectedRemainingRecords,{"JoinColumn"}),
        SelectedNewRecords = Table.NestedJoin(RemainingRecords,{"InventoryID"},TableSoFar,{"Materials"},"RemainingRecords",JoinKind.Inner),
        NewRecords = Table.ExpandTableColumn(SelectedNewRecords, "RemainingRecords", {"Finished InventoryID"}),
        NewTable = Table.Buffer(TableSoFar & NewRecords),
        Result = if Table.IsEmpty(SelectedRemainingRecords) then TableSoFar else @ExplodeBOM(NewTable, RemainingRecords)
    in
        Result,

    ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,#"BI - BOM Material_table"),
    Sorted = Table.Sort(ExplodedBOM,{{"Finished InventoryID", Order.Ascending}, {"Materials", Order.Ascending}}),
    Reordered = Table.ReorderColumns(Sorted,{"Finished InventoryID", "InventoryID", "Materials"}) 
in
    Reordered

Troubleshooting your issues takes me a multitude of time that was required to come up with a solution in the first place.

 

Again, your ExplodedBOM step is wrong.

 

It should be:

 

    ExplodedBOM = ExplodeBOM(AddedFinishedInventoryID,#"Removed Duplicates"),

 

My suggestion would be not to use any solution you don't understand.

Specializing in Power Query Formula Language (M)

Hi Marcel

 

Thanks very much for the help!

 

Greatly appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.