simplify code

[roelandgeorge]

Hi, 
I'm working on a bill of material (see for background information: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculating-with-values-linked-throu...)

Now i have a very long code, but it goes to slow. I'm wondering if I could condense it. Because it takes hours to load with 90k rows. One thing where I can improve it I think is in this line:

#"Merged Columns2" = Table.CombineColumns( #"Merged Columns" ,{LevelColumnName, "Name.1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),LevelColumnName)

The values here (type text) are combined with a delimiter, but I want to multiplicate them as numbers. That should already be an efficient shortcut. How do I do that? 

Here is the link to the file or below is the full code:
bom 5.0 example.pbix

let
ParChTable = BOM,
ChildKey = "Node",
ParentKey = "FirstParent",
LevelColumnName = "Amount",
SelectRelevantColumns = Table.SelectColumns(ParChTable, {ChildKey, ParentKey, LevelColumnName}),
#"Changed Type" = Table.TransformColumnTypes(SelectRelevantColumns ,{{ChildKey, type text}, {ParentKey, type text}}),
ReplaceNulls = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{ParentKey}),
MissingParents = List.Buffer(List.Select(List.Difference(List.Distinct(Table.Column(ReplaceNulls , ParentKey)), List.Distinct(Table.Column(ReplaceNulls , ChildKey))), each _ <> "")),
AddMissingParents = Table.Buffer(Table.Combine({ReplaceNulls , #table({ChildKey, LevelColumnName, ParentKey}, List.Transform(MissingParents, each {_, "Unknown TopLevel"& Text.From(List.PositionOf(MissingParents, _)), ""}))})),
#"Merged Queries0" = Table.NestedJoin(AddMissingParents,{ChildKey},AddMissingParents,{ParentKey},"SelectRelevantColumns",JoinKind.LeftOuter),
CheckIfIsLeaf = Table.AddColumn(#"Merged Queries0", "IsLeaf", each if Table.IsEmpty([SelectRelevantColumns]) then "yes" else "no"),
#"Replaced Value1" = Table.ReplaceValue(CheckIfIsLeaf,null,"",Replacer.ReplaceValue,{ParentKey, LevelColumnName}),
AddStartPath = Table.AddColumn(#"Replaced Value1", "Path", each Text.Trim(Record.Field(_, ChildKey)&"|"&Record.Field(_,ParentKey), "|")),
#"Duplicated Column" = Table.DuplicateColumn(AddStartPath, LevelColumnName, "FirstName"),
Feed = Table.DuplicateColumn(#"Duplicated Column", ParentKey, "FirstParentKey"),
fnAllParents = List.Generate(()=>
[Result= Feed, Level=1, EndlessLoop = false, StopEndlessLoop = false],
each Table.RowCount([Result]) > 0 and not [StopEndlessLoop],
each [ Result= let
#"Merged Queries" = Table.NestedJoin([Result],{ParentKey},AddMissingParents,{ChildKey},"Added Custom",JoinKind.Inner),
#"Removed Columns1" = Table.RemoveColumns(#"Merged Queries",{ParentKey}),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Added Custom", {ParentKey, LevelColumnName}, {"ParentKey.1", "Name.1"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Expanded Added Custom", "ParentKey.1", ParentKey),
#"Merged Columns" = Table.CombineColumns(#"Duplicated Column",{"Path", "ParentKey.1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Path"),
#"Merged Columns2" = Table.CombineColumns( #"Merged Columns" ,{LevelColumnName, "Name.1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),LevelColumnName)
in
Table.Buffer(#"Merged Columns2"),
Level = [Level]+1,
EndlessLoop = List.Sort(List.Distinct(Table.Column(Result, ChildKey))) = List.Sort(List.Distinct(Table.Column([Result], ChildKey))),
StopEndlessLoop = [EndlessLoop]
]),
ConvertToTable = Table.FromList(fnAllParents, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandLevel = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"Result", "Level", "EndlessLoop"}, {"Result", "Level", "EndlessLoop"}),
ExpandLG = Table.ExpandTableColumn(ExpandLevel, "Result", {LevelColumnName, ParentKey, ChildKey, "Path", "FirstName", "FirstParentKey"}, {"Name", "ParentKey", "NodeKey", "Path", "FirstName", "FirstParentKey"}),
FilterParents = Table.SelectRows(ExpandLG, each ([ParentKey] = null or [ParentKey] = "")),
#"Removed Columns" = Table.RemoveColumns(FilterParents,{"ParentKey"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Path", each Text.Trim(_, "|")}}),
ReverseOrderName = Table.TransformColumns(#"Trimmed Text",{{"Name", each Text.Combine(List.Reverse(Text.Split(_, "|")), "|")}}),
ReverseOrderPath = Table.TransformColumns(ReverseOrderName,{{"Path", each Text.Combine(List.Reverse(Text.Split(_, "|")), "|")}}),
#"Reordered Columns" = Table.ReorderColumns(ReverseOrderPath,{"NodeKey", "FirstParentKey", "Path", "FirstName", "Level", "Name"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Name", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Transform({1..Table.RowCount(ConvertToTable)}, each "Level "&Text.From(_))),
#"Merged Queries" = Table.NestedJoin(#"Split Column by Delimiter",{"NodeKey", "FirstParentKey"},ParChTable ,{ChildKey, ParentKey},"Split Column by Delimiter",JoinKind.LeftOuter),
#"Expanded Split Column by Delimiter" = Table.ExpandTableColumn(#"Merged Queries", "Split Column by Delimiter", List.Difference(Table.ColumnNames(ParChTable), Table.ColumnNames(#"Replaced Value1"))),
Rename = Table.RenameColumns(#"Expanded Split Column by Delimiter",{{"Level", "HierarchyDepth"}}),
Parents = List.Buffer(Rename[FirstParentKey]),
IsLeaf = Table.AddColumn(Rename, "IsLeaf", each not List.Contains(Parents, [NodeKey])),
#"Filtered Rows" = Table.SelectRows(IsLeaf, each ([IsLeaf] = true)),
#"Removed Unnecessary Columns" = Table.SelectColumns( #"Filtered Rows",{"NodeKey", "Path", "Level 1", "Level 2", "Level 3", "Level 4", "Level 5"}),
#"Changed Type to multiplicate" = Table.TransformColumnTypes(#"Removed Unnecessary Columns",{{"Level 1", type number}, {"Level 2", type number}, {"Level 3", type number}, {"Level 4", type number}, {"Level 5", type number}, {"NodeKey", type text}, {"Path", type text}}),
#"Added Multiplication Column" = Table.AddColumn(#"Changed Type to multiplicate", "RESULT", each (if [Level 1] <> null then [Level 1] else 1) * (if [Level 2] <> null then [Level 2] else 1) * (if [Level 3] <> null then [Level 3] else 1) * (if [Level 4] <> null then [Level 4] else 1) * (if [Level 5] <> null then [Level 5] else 1)),
#"Changed Result type to percentage" = Table.TransformColumnTypes(#"Added Multiplication Column",{{"RESULT", Percentage.Type}}),
#"Split Column by Positions" = Table.SplitColumn(#"Changed Result type to percentage", "Path", Splitter.SplitTextByPositions({0, 7}), {"Path.1", "Path.2"}),
#"Removed Unnecssary Columns" = Table.SelectColumns(#"Split Column by Positions",{"NodeKey", "Path.1", "RESULT"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Unnecssary Columns",{{"NodeKey", "Raw material"}, {"Path.1", "End product"}, {"RESULT", "Percentage raw material in end product"}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns",{"End product", "Raw material", "Percentage raw material in end product"})
in
#"Reordered Columns2"

[hackcrr]

Hi, @roelandgeorge 
Maybe you can try the following M code:

// Assume that "Name.1" is a text column, and that you can safely convert it to a number  
let  
    Source = ... , // your original data source  
    // ... Other conversion steps ...  
    // Assuming "Merged Columns" is the result of one of your previous steps  
    #"Converted Name.1 to Number" = Table.TransformColumns(  
        #"Merged Columns", {{"Name.1", {}  
        {{"Name.1", each Number.FromText(_, "en-US"), type number}} // Convert the "Name.1" column to a number, assuming the "en-US" locale is used here.  
    ), # "Added Multiplied Columns", type number}}  
    #"Added Multiplied Column" = Table.AddColumn(  
        #"Converted Name.1 to Number", #"MultipliedValue", #"MultipliedColumn" = Table.AddColumn(  
        "MultipliedValue", // the name of the new column  
        each [LevelColumnName] * [Name.1], // Multiply LevelColumnName with Name.1  
        type number // set the data type of the new column to number  
    )  
in  
    # "Added Multiplied Column"

In this way, your code should be more efficient, as it avoids unnecessary text merge operations and performs direct number crunching.

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[roelandgeorge]

The line where you wanna insert it is inside the recursive Function for the Parent Traversal? But seems like you wrote it like it's regular...