Recursive assembly tree

Alexelbertse · ‎12-23-2024

Hello,

have searched a lot, use ai, copilot etc. But I don't seem to get any real working solution.

we use erp and sell assembly's. There can be sub-assembly's and so on. Each assembly contains material and work activity.

I want to have an report which the user selects an order number with an slicer. Based on that order number I would like to have al the assembly's and sub-assembly's with the items and work activity's.

next I want to calculate the totals based on the assembly and work activity.

I have and stored procedure which returns the correct data in sql, but in powerbi it is not working.

I tried witch stored procedure, sql view, sql function.

tabels:

order ( for the order number and corresponding pk)

Salesorderassembly ( get sold assembly's based on order fk)

assemblydetailitem ( items in an assembly fk for lookup)

assemblydetailworkactivity ( workactivty in an assembly fk for lookup)

I hope someone can help me create this report, or point me in the right direction.

Alexelbertse · ‎01-02-2025

@PwerQueryKees @lbendlin
we use SQL Server 2022.

sample excel with data. https://we.tl/t-hfxlL5re9t

Output expected is

What i would like to achive is when user inpunts an order with the slicer, Then i would like to now how much production time is needed per main assembly and its subassemblys. Sometimes there are more then one assembly sold.

V_R_ORDER( for the order number and corresponding pk)

V_R_SALESORDERDETAILASSEMBLY( get sold assembly's based on order fk)

V_R_ASSEMBLYDETAILWORKACTIVITY( workactivty in an assembly fk for lookup)

V_R_ASSEMBLYDETAILSUBASSEMBLY( Subassemblys in an assembly fk for lookup)

I`ve tried with this M-code, but it just keeps trying and then just times out.

let
    // Verbind met de SQL Server database
    Bron = Sql.Database(ServerName, DatabaseName),

    // Laad de benodigde tabellen
    R_ASSEMBLYDETAILSUBASSEMBLY = Bron{[Schema="dbo",Item="R_ASSEMBLYDETAILSUBASSEMBLY"]}[Data],
    R_ASSEMBLY = Bron{[Schema="dbo",Item="R_ASSEMBLY"]}[Data],

    // Begin met de basis assembly waar FK_ASSEMBLY = 147468
    Source = Table.SelectRows(R_ASSEMBLYDETAILSUBASSEMBLY, each [FK_ASSEMBLY] = 147468),
    
    // Converteer het initiële resultaat om Path en Level kolommen te hebben
    InitialAssembly = Table.AddColumn(Source, "Path", each Text.From([PK_R_ASSEMBLYDETAILSUBASSEMBLY], "en-US")) & 
                      Table.AddColumn(_, "Level", each 0),
    
    // Buffer het initiële resultaat
    BufferedInitialAssembly = Table.Buffer(InitialAssembly),

    // Recursieve functie om alle subassemblies te krijgen
    RecursiveSubassemblies = (currentTable as table) as table =>
        let
            // Haal subassemblies op voor elke assembly in de huidige tabel
            Subassemblies = Table.NestedJoin(currentTable, {"FK_SUBASSEMBLY"}, R_ASSEMBLYDETAILSUBASSEMBLY, {"FK_ASSEMBLY"}, "Subassemblies", JoinKind.LeftOuter),
            
            // Vouw de geneste tabel uit om subassemblies te krijgen
            Expanded = Table.ExpandTableColumn(Subassemblies, "Subassemblies", {"PK_R_ASSEMBLYDETAILSUBASSEMBLY", "FK_ASSEMBLY", "FK_SUBASSEMBLY"}, {"Sub_PK_R_ASSEMBLYDETAILSUBASSEMBLY", "Sub_FK_ASSEMBLY", "Sub_FK_SUBASSEMBLY"}),
            
            // Construheer nieuw pad en verhoog niveau
            WithNewPath = Table.AddColumn(Expanded, "NewPath", each [Path] & "." & Text.From([Sub_PK_R_ASSEMBLYDETAILSUBASSEMBLY], "en-US")),
            WithNewLevel = Table.TransformColumns(WithNewPath, {"Level", each _ + 1, type number}),
            
            // Selecteer relevante kolommen
            SelectedColumns = Table.SelectColumns(WithNewLevel, {"Sub_PK_R_ASSEMBLYDETAILSUBASSEMBLY", "Sub_FK_ASSEMBLY", "Sub_FK_SUBASSEMBLY", "NewPath", "Level"}),
            
            // Hernoem voor consistentie
            Renamed = Table.RenameColumns(SelectedColumns, 
                {{"Sub_PK_R_ASSEMBLYDETAILSUBASSEMBLY", "PK_R_ASSEMBLYDETAILSUBASSEMBLY"}, 
                 {"Sub_FK_ASSEMBLY", "FK_ASSEMBLY"}, 
                 {"Sub_FK_SUBASSEMBLY", "FK_SUBASSEMBLY"}, 
                 {"NewPath", "Path"}}),
            
            // Combineer de huidige laag met de nieuwe laag
            Combined = Table.Combine({currentTable, Renamed}),
            
            // Als er geen subassemblies meer zijn, retourneer; anders, ga door met recursie
            RecursiveCall = if Table.IsEmpty(Renamed) then Combined else @RecursiveSubassemblies(Renamed)
        in
            RecursiveCall,
    
    // Start de recursie met initiële gegevens
    AllSubassemblies = RecursiveSubassemblies(BufferedInitialAssembly),

    // Buffer het resultaat van alle subassemblies
    BufferedAllSubassemblies = Table.Buffer(AllSubassemblies),

    // Voeg R_ASSEMBLY toe om CODE en REVISION te krijgen
    JoinedWithAssembly = Table.NestedJoin(BufferedAllSubassemblies, {"FK_SUBASSEMBLY"}, R_ASSEMBLY, {"PK_R_ASSEMBLY"}, "AssemblyDetails", JoinKind.Inner),
    ExpandedAssemblyDetails = Table.ExpandTableColumn(JoinedWithAssembly, "AssemblyDetails", {"CODE", "REVISION"}, {"CODE", "REVISION"}),

    // Sorteer op Path voor hiërarchische weergave
    Sorted = Table.Sort(ExpandedAssemblyDetails, {{"Path", Order.Ascending}}),

    // Eindtransformatie voor weergave van het pad
    FinalResult = Table.AddColumn(Sorted, "DisplayPath", each if [Level] > 0 then [Path] else Text.From([PK_R_ASSEMBLYDETAILSUBASSEMBLY], "en-US")),
    SelectedFinal = Table.SelectColumns(FinalResult, {"PK_R_ASSEMBLYDETAILSUBASSEMBLY", "CODE", "REVISION", "DisplayPath", "Level"})
in
    SelectedFinal

check and correct this. It is based on this SQL querie but want to use in powerbi. 
WITH RecursiveAssembly AS (
    -- Anchor member: De basis assembly
    SELECT 
        PK_R_ASSEMBLYDETAILSUBASSEMBLY,
        FK_ASSEMBLY,
        FK_SUBASSEMBLY,
        CAST(PK_R_ASSEMBLYDETAILSUBASSEMBLY AS NVARCHAR(2000)) AS Path,
        0 AS Level
    FROM 
        R_ASSEMBLYDETAILSUBASSEMBLY
    WHERE 
        FK_ASSEMBLY = 147468

    UNION ALL

    -- Recursive member: Voor elke subassembly, haal de subassemblies op
    SELECT 
        d.PK_R_ASSEMBLYDETAILSUBASSEMBLY,
        d.FK_ASSEMBLY,
        d.FK_SUBASSEMBLY,
        CAST(ra.Path + '.' + CAST(d.PK_R_ASSEMBLYDETAILSUBASSEMBLY AS NVARCHAR) AS NVARCHAR(2000)) AS Path,
        ra.Level + 1
    FROM 
        R_ASSEMBLYDETAILSUBASSEMBLY d
    INNER JOIN 
        RecursiveAssembly ra ON d.FK_ASSEMBLY = ra.FK_SUBASSEMBLY
)
-- Query die de resultaten uit v_R_assemblydetailworkactivity haalt voor alle gevonden subassemblies
SELECT 
    v.*,
    CASE WHEN ra.Level > 0 THEN CAST(ra.Path AS NVARCHAR) ELSE CAST(ra.PK_R_ASSEMBLYDETAILSUBASSEMBLY AS NVARCHAR) END AS Path,
    ra.Level
FROM 
    RecursiveAssembly ra
LEFT JOIN 
    v_R_assemblydetailworkactivity v ON v.FK_ASSEMBLY = ra.FK_SUBASSEMBLY
ORDER BY 
    ra.Level, ra.Path;

PwerQueryKees · ‎01-03-2025

Looking at the sample data, why do you need recursion?
The "Path" gives you all children in 1 go.

I made a start of a solution for you, because I don't fully understand you data. I hope it helps you.

This:

let
    Source = ASSEMBLYDETAILSUBASSEMBLY,  // From you sample data NOTE: The path has Type text!!!
    // Selectrows to get all children of this row as a new column. My ASSUMPTION: The row itself should  be included
    #"Added Custom" = Table.AddColumn(Source, "Child", each Table.SelectRows(Source, (child) =>  Text.StartsWith(child[Path], [Path]))),
    #"Expanded Child" = Table.ExpandTableColumn(#"Added Custom", "Child", {"PK_R_ASSEMBLYDETAILSUBASSEMBLY"}, {"Child.PK_R_ASSEMBLYDETAILSUBASSEMBLY"}), // Get the subassemply key and add a row for each child.
    #"Merged Queries" = Table.NestedJoin(#"Expanded Child", {"Child.PK_R_ASSEMBLYDETAILSUBASSEMBLY"}, ASSEMBLYDETAILWORKACTIVITY, {"FK_ASSEMBLY"}, "ASSEMBLYDETAILWORKACTIVITY", JoinKind.LeftOuter),
// you could/should place an additional filter here I guess???
    #"Expanded ASSEMBLYDETAILWORKACTIVITY" = Table.ExpandTableColumn(#"Merged Queries", "ASSEMBLYDETAILWORKACTIVITY", {"OPERATIONTIME", "SETUPTIME"}, {"DETAILWORK.OPERATIONTIME", "DETAILWORK.SETUPTIME"}), // get the times
    // Group the rows back to the original records and sum the time fields from ASSEMBLYDETAILWORKACTIVITY 
    #"Grouped Rows" = Table.Group(#"Expanded ASSEMBLYDETAILWORKACTIVITY", {"PK_R_ASSEMBLYDETAILSUBASSEMBLY", "CODE", "Path", "Level"}, {{"Detail Count", each Table.RowCount(_), Int64.Type}, {"Operation Time", each List.Sum([PK_R_ASSEMBLYDETAILSUBASSEMBLY]), type nullable number}, {"Setup Time", each List.Sum([DETAILWORK.SETUPTIME]), type nullable number}})
    // add a filter here to get the data at the level you want.
in
    #"Grouped Rows"

Produces this:

Alexelbertse · ‎01-06-2025

Thank you, I’ll give this a try.

I need recursion because the subassembly list contains subassemblies, but a subassembly could have another subassembly, and so on. Therefore, I need recursion because I only know the main assembly that is sold. I can easily look up the first subassembly I know, but for deeper levels, I need recursion.

PwerQueryKees · ‎01-08-2025

I understand you need subassemlies using subassemblies. My example solution uses the column "Path" to find all parts used by all subassemplies, including all subassemplies used by those subassemblies without using a recursive function.

Recursion is definitely feasible, (I saw you used it already yourself), but in general Power Query does not perform very well on large volumes of data with a recursive structure.

PwerQueryKees · ‎12-27-2024

If you send some before and after sample data, I can give the M Code a try, with comments to help you understand.

What is the DBMS you are using?

PwerQueryKees · ‎12-25-2024

So, essentially, what you want is to have a 'flattended" assembly table giving so you can join this with the detail and workitems tables for you report?

You would need a recursive function in PowerQuery to do this.

This is doable, but complicated. It needs manual M code programming. I, and may others on this forum could do this.

Two words of warning:

Performance may be an issue for highre volumes. I have done a recursive function on 10000 rows and it took over 10 minutes to complete on a high end laptop in excel. And response times will grow quadratically with volume.
The resulting function may not be easy to understand without sufficient M code knowledge. I don't know your level of expertise or the expertise level of your company, but you might want to consider maintainability before going this route.

Depending on the SQL dialect used by your DBMS, you could consider solving it in SQL. DataBricks should be able to do this. Also complicated probably, but SQL expertise is more wide spread and performance may be a lot better. I have no experience with solving this kind of problem in DataBricks and I have no access to any advanced SQL implementation at the moment, so I probably would not be the best person to help you on an SQL solution.

If my assumption on what you want to achieve is correct AND you want to pursue a M code solution, let us know. I might give it a go and otherwise there are many more experts here that could do it.

A bit more sample data, perferably as a downloadable file from any file hosting platform (Google, SharePoint, DropBox, etc) would help! Please also include a sample of your expected result!

Kees Stolker

A big fan of Power Query and Excel

Alexelbertse · ‎12-26-2024

thank you for your repley. SQL knowledge is pressent. M-Code, where learning as we go 🙂

i managed to "solve" the problem in SQL with the stored procedure. I join`t the relevant collums there and it returns me most of the data i want now. But the problem with the stored procedure is, i cannot pass the parameters to it every time some body change the slicer input.

I have a parameter OrderNumer wich i connected to the slicer. This works, but when changing the slicer, the stored procedure is not returning the data. When in advanced qeurie editor and altering the parameter there the qeury with the stored procedure is used directly and returning me the data.

But when in the report view not. When refreshing in report view i get this error:
Non-null assertion failure: resource: Resource should be available for query V_R_SALESORDERDETAILASSEMBLY to store FoldedArtifacts in cache

But when refreshing in the advanced qeurie editor, this error does not show.

So, i would like to know and learn how to use the manual M code to recursive get the data i need. Would like to test it to see if it works for us.

But if possible, also for peformance reasons to use the Stored Procedure with CTE`s i think i would prefere that.

Alexelbertse · ‎12-23-2024

also would like to know how i can show the hyarchie based on the ID or parrentId data?

Alexelbertse · ‎12-23-2024

Te stored procedure returns me this. The detailId is the Primary Key of the table it is from.

DetailId	DetailType	Id	code	parentId	quantity	DOCCOUNT	AssemblyId	DetailLevel	IsDetail
147468	1	0.147468	107831-0001-000	0	0	2	147468	0	False
47837	2	0.147468.45835.47837	107831-0003-005	0.147468.45835	1	1	147547	2	False
47838	2	0.147468.45835.47838	107831-0003-006	0.147468.45835	1	1	147548	2	False
47839	2	0.147468.45835.47839	107831-0003-200	0.147468.45835	1	0	147480	2	False
45923	2	0.147468.45835.47839.45923	107831-0003-012	0.147468.45835.47839	1	1	147554	3	False
47845	2	0.147468.45835.47845	107831-0004-003	0.147468.45835	1	0	147896	2	False
571353	3	0.147468.45836.47777.45851.I571353	MAGPLG4.00 ZM310	0.147468.45836.47777.45851	0,013	0	147505	1	True
572094	3	0.147468.45836.47777.45851.I572094	PEMFH0515	0.147468.45836.47777.45851	2	0	147505	1	True
572065	3	0.147468.45836.47777.45852.I572065	PEMFH0520	0.147468.45836.47777.45852	2	0	147506	1	True
571354	3	0.147468.45836.47777.45852.I571354	MAGPLG4.00 ZM310	0.147468.45836.47777.45852	0,004	0	147506	1	True
571356	3	0.147468.45836.47777.45853.45874.I571356	BPLG8.00	0.147468.45836.47777.45853.45874	0,009	0	147508	1	True
571357	3	0.147468.45836.47777.45853.45875.I571357	BPLK10.00	0.147468.45836.47777.45853.45875	0,00266262	0	147509	1	True
635029	6	0.147468.45836.47778.45856.W635029	8000	0.147468.45836.47778.45856	1	1	147490	1	True
635039	6	0.147468.45836.47778.45856.W635039	1200	0.147468.45836.47778.45856	1	1	147490	1	True
635040	6	0.147468.45836.47778.45856.W635040	1200	0.147468.45836.47778.45856	1	0	147490	1	True
635041	6	0.147468.45836.47778.45856.W635041	6000	0.147468.45836.47778.45856	1	1	147490	1	True
635042	6	0.147468.45836.47778.45856.W635042	6100	0.147468.45836.47778.45856	1	1	147490	1	True
637374	6	0.147468.45836.47778.45856.W637374	1500	0.147468.45836.47778.45856	1	0	147490	1	True
633834	6	0.147468.45836.47778.45856.45881.W633834	3900	0.147468.45836.47778.45856.45881	1	1	147515	1	True
633835	6	0.147468.45836.47778.45856.45881.W633835	5000	0.147468.45836.47778.45856.45881	1	1	147515	1	True
633837	6	0.147468.45836.47778.45856.45882.W633837	3900	0.147468.45836.47778.45856.45882	1	1	147516	1	True
633838	6	0.147468.45836.47778.45856.45882.W633838	5000	0.147468.45836.47778.45856.45882	1	1	147516	1	True
633840	6	0.147468.45836.47778.45856.45883.W633840	3900	0.147468.45836.47778.45856.45883	1	1	147517	1	True
635075	6	0.147468.45836.47778.45857.W635075	1112	0.147468.45836.47778.45857	1	1	147491	1	True
635028	6	0.147468.45836.47778.45857.45884.W635028	5200	0.147468.45836.47778.45857.45884	1	1	147518	1	True
633843	6	0.147468.45836.47778.45857.45884.W633843	5000	0.147468.45836.47778.45857.45884	1	1	147518	1	True
633842	6	0.147468.45836.47778.45857.45884.W633842	3700	0.147468.45836.47778.45857.45884	1	1	147518	1	True
633845	6	0.147468.45836.47778.45857.45885.W633845	3700	0.147468.45836.47778.45857.45885	1	1	147519	1	True

SalesOrderAssembly, that is detailType 0. The assembly that is sold. Detail Type 2 is subassemblys (recursyve). The parrentId shows the hyarchie. detailType 3 are the assembly Items. detailType 6 are the workactivitys.

What i would like to achive is when user inpunts an order with the slicer, the ordernmuber is passed to the Query that uses the stored procedure. Then i would like to now how much production time is needed per main assembly and its subassemblys. Sometimes there are more then one assembly sold.

something like this:

Anonymous · ‎12-23-2024

Hi @Alexelbertse ,

-- I have and stored procedure which returns the correct data in sql, but in powerbi it is not working.

Total Cost = 
SUMX(
SUMMARIZE(
    'AssemblyDetailItem',
    'AssemblyDetailItem'[ItemId],
    'SalesOrderAssembly'[AssemblyId],
    'SalesOrderAssembly'[Quantity],
    'Order'[OrderId],
    "@cost", SUM('AssemblyDetailItem'[Cost])
),
'SalesOrderAssembly'[Quantity]*[@cost]
)

Total Hours = 
SUMX(
SUMMARIZE(
    'AssemblyDetailWorkActivity',
    'AssemblyDetailWorkActivity'[ActivityId],
    'SalesOrderAssembly'[AssemblyId],
    'SalesOrderAssembly'[Quantity],
    'Order'[OrderId],
    "@hours", SUM('AssemblyDetailWorkActivity'[Hours])
),
'SalesOrderAssembly'[Quantity]*[@hours]
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

lbendlin · ‎12-23-2024

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...