Forum Discussion

DiKi-I's avatar
DiKi-I
Post Partisan
2 years ago

Power query help

Hi , I have multiple relationship table around 5 to 6 which are available in mutiple tables. I'm attaching some sample data there are 3 tables table1, table2, table3. Realtionship is from parent...
Need Help
DiKi-I's avatar
DiKi-I
Post Partisan
2 years ago

Hi,

I tested the above and now I am getting the result quickly but if the values are same I am getting null in the child only getting the value in the first parent.
values are below in my data
business service= A

business service line =A

department = A
and I final result I am getting from you m query is: 

business service= A

business service line =null

department = null

 

 

my data is like this

 

spinfuzer's avatar
spinfuzer
Solution Sage
2 years ago

merging by parent and parentclassname instead of just parent.

 

 

 

 

 

 

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUoqLc7MSy0uVihOLSrLTE4FCrlgEVbIAXKVYnWilZywa3LFq8kZuyY3cjS5k6MpAq8mRxwhgVUcqi0WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Parentclassname = _t, Child = _t, Childclassname = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclHSUUoqLc7MSy0uVihOLSrLTE5VyAFygeIeQJySWpBYVJKbmleiFKsTreSKR7knpnI3PMq9MJW741HuTZpyH0zlEXiUR4HcHwJW5uiIRx1YEtncWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Parentclassname = _t, Child = _t, Childclassname = _t]),
    Table3 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8lDSUUpJLUgsKslNzSsBcnyB2DNEKVYnWskTXc4PIeeFLuePkPNGlwvAIxeIkPNBlwuCysUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Parentclassname = _t, Child = _t, Childclassname = _t]),
    Combined = Table.Buffer(Table.Combine({Table1,Table2,Table3})),
    tree = (tbl as table, optional n as number) =>
        let
            update_tree =
                
                if List.Contains(Table.ColumnNames(tbl),"tree")
                then 
                    // Change to Parent Columns
                    Table.ReplaceValue(tbl, each [tree], each Record.AddField([tree], [Parentclassname], [Parent]), Replacer.ReplaceValue,{"tree"})
                else    
                    Table.AddColumn(tbl,"tree", each Record.AddField([], [Parentclassname], [Parent])), // Change to Parent Column
            removeOldParent = Table.RemoveColumns(update_tree, {"Parent", "Parentclassname"}), // Change to Parent Column
            renameChildToParent = Table.RenameColumns(removeOldParent, {{"Child","Parent"},{"Childclassname","Parentclassname"}}),
            getNextChild = if List.NonNullCount(renameChildToParent[Parent]) <> 0 and n < 10 // remove and n < 10 after checking for loops
                then 
                    @tree(
                        Table.ExpandTableColumn(
                            Table.NestedJoin(
                                renameChildToParent,{"Parent","Parentclassname"}, // Change to Parent Column
                                Combined,{"Parent","Parentclassname"}, // Change to Parent Column
                                "nextChild",
                                JoinKind.LeftOuter
                            )
                            ,
                            "nextChild",
                            {"Child","Childclassname"} // Change to Child Column

                        )
                    , n+1 // remove this check
                    )
                else Table.SelectColumns(renameChildToParent,"tree")
        in
            getNextChild,
    final = tree(Table1,0), // remove the ,0 after fixing query and fixing infinite loops
    #"Expanded tree" = Table.ExpandRecordColumn(final, "tree", {"business service", "business service line", "department", "IT"}, {"business service", "business service line", "department", "IT"})        

in
    #"Expanded tree"