How to manually add a query in Power Query – AssetTreeSpatial example –

In this short tutorial, we will see how to manually insert a query in Power Query by using copy-paste from an existing query in the Advanced editor available in Power Quiery (Microsoft Power BI Desktop)

Our goal here is to add the table “AssetTreeSpatial” inside our pre-made power-bi template downloaded from SeveUp app to create your BIM dashboard.

What is the Spatial tree in your BIM models ?

All your Bim models are organized around a spatial tree, this is essential to correctly locate the different elements that compose the BIM model. Each element is attached to a level of this tree structure.

In the case described in this post, we use the data from the BIM models in .ifc format that you uploaded to SeveUp app previously

The spatial tree can be imaged as follows

Why you need to add the table “AssetTreeSpatial” in your SeveUp Pre-made Microsoft power BI template ?

Right after uploading your BIM models to SeveUp app, we convert them into a database and provide you with power BI-Pre-made templates

In these templates, the basic tables you will find are the following

• Qto
• Assets
• AssetTree
• Properties

We are continuously working to add new tables as time goes by and according to the use cases and you can find as other tables the following ones:

• Models
• Classification
• Materials
• AssetTypes
• AssetTreeSpatial
• …

In the next step of this post, We will show you how to add manually the table “AssetTreeSpatial” if it is not in the list of tables in your pre-made Microsoft Power BI template (from SeveUp app), Power query section.

How to add manually the table “AssetTreeSpatial” in Power Query ?

• Open power Query by clicking on transform data
• Right click on Assets table
• Duplicate

• Right-click on Assets (2)
• Open advanced editor

• Select all
• Delete All

copy the query below

let
    Source = AssetTree,
    #"Filtered Rows" = Table.SelectRows(Source, each true),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"ChildAssetId"}, Assets, {"AssetId"}, "Assets", JoinKind.LeftOuter),
    #"Expanded Assets" = Table.ExpandTableColumn(#"Merged Queries", "Assets", {"Class"}, {"Assets.Class"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Assets", {"ChildAssetId"}, Assets, {"AssetId"}, "Assets", JoinKind.LeftOuter),
    #"Expanded Assets1" = Table.ExpandTableColumn(#"Merged Queries1", "Assets", {"Name"}, {"Assets.Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Assets1",{{"Assets.Class", "Level0Class"}, {"Assets.Name", "Level0Name"}}),
    #"Merged Queries2" = Table.NestedJoin(#"Renamed Columns", {"ParentAssetId"}, Assets, {"AssetId"}, "Assets", JoinKind.LeftOuter),
    #"Expanded Assets2" = Table.ExpandTableColumn(#"Merged Queries2", "Assets", {"Class"}, {"Assets.Class"}),
    #"Merged Queries3" = Table.NestedJoin(#"Expanded Assets2", {"ParentAssetId"}, Assets, {"AssetId"}, "Assets", JoinKind.LeftOuter),
    #"Expanded Assets3" = Table.ExpandTableColumn(#"Merged Queries3", "Assets", {"Name"}, {"Assets.Name"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Assets3",{{"Assets.Class", "Level1Class"}, {"Assets.Name", "Level1Name"}}),
    #"Merged Queries4" = Table.NestedJoin(#"Renamed Columns1", {"ChildAssetId"}, AssetTree, {"ChildAssetId"}, "AssetTree", JoinKind.LeftOuter),
    #"Expanded AssetTree" = Table.ExpandTableColumn(#"Merged Queries4", "AssetTree", {"ParentAssetId"}, {"AssetTree.ParentAssetId"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded AssetTree",{{"AssetTree.ParentAssetId", "Level2Id"}}),
    #"Merged Queries5" = Table.NestedJoin(#"Renamed Columns2", {"Level2Id"}, Assets, {"AssetId"}, "Assets", JoinKind.LeftOuter),
    #"Expanded Assets4" = Table.ExpandTableColumn(#"Merged Queries5", "Assets", {"Class"}, {"Assets.Class"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Expanded Assets4",{{"Assets.Class", "Level2Class"}}),
    #"Merged Queries6" = Table.NestedJoin(#"Renamed Columns3", {"Level2Id"}, Assets, {"AssetId"}, "Assets", JoinKind.LeftOuter),
    #"Expanded Assets5" = Table.ExpandTableColumn(#"Merged Queries6", "Assets", {"Name"}, {"Assets.Name"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Expanded Assets5",{{"Assets.Name", "Level2Name"}}),
    #"Merged Queries7" = Table.NestedJoin(#"Renamed Columns4", {"Level2Id"}, AssetTree, {"ChildAssetId"}, "AssetTree", JoinKind.LeftOuter),
    #"Expanded AssetTree1" = Table.ExpandTableColumn(#"Merged Queries7", "AssetTree", {"ParentAssetId"}, {"AssetTree.ParentAssetId"}),
    #"Renamed Columns5" = Table.RenameColumns(#"Expanded AssetTree1",{{"AssetTree.ParentAssetId", "Level3Id"}}),
    #"Merged Queries8" = Table.NestedJoin(#"Renamed Columns5", {"Level3Id"}, Assets, {"AssetId"}, "Assets", JoinKind.LeftOuter),
    #"Expanded Assets6" = Table.ExpandTableColumn(#"Merged Queries8", "Assets", {"Class"}, {"Assets.Class"}),
    #"Renamed Columns6" = Table.RenameColumns(#"Expanded Assets6",{{"Assets.Class", "Level3Class"}}),
    #"Merged Queries9" = Table.NestedJoin(#"Renamed Columns6", {"Level3Id"}, Assets, {"AssetId"}, "Assets", JoinKind.LeftOuter),
    #"Expanded Assets7" = Table.ExpandTableColumn(#"Merged Queries9", "Assets", {"Name"}, {"Assets.Name"}),
    #"Renamed Columns7" = Table.RenameColumns(#"Expanded Assets7",{{"Assets.Name", "Level3Name"}}),
    #"Merged Queries10" = Table.NestedJoin(#"Renamed Columns7", {"Level3Id"}, AssetTree, {"ChildAssetId"}, "AssetTree", JoinKind.LeftOuter),
    #"Expanded AssetTree2" = Table.ExpandTableColumn(#"Merged Queries10", "AssetTree", {"ParentAssetId"}, {"AssetTree.ParentAssetId"}),
    #"Renamed Columns8" = Table.RenameColumns(#"Expanded AssetTree2",{{"AssetTree.ParentAssetId", "Level4Id"}}),
    #"Merged Queries11" = Table.NestedJoin(#"Renamed Columns8", {"Level4Id"}, Assets, {"AssetId"}, "Assets", JoinKind.LeftOuter),
    #"Expanded Assets8" = Table.ExpandTableColumn(#"Merged Queries11", "Assets", {"Class"}, {"Assets.Class"}),
    #"Renamed Columns9" = Table.RenameColumns(#"Expanded Assets8",{{"Assets.Class", "Level4Class"}}),
    #"Merged Queries12" = Table.NestedJoin(#"Renamed Columns9", {"Level4Id"}, Assets, {"AssetId"}, "Assets", JoinKind.LeftOuter),
    #"Expanded Assets9" = Table.ExpandTableColumn(#"Merged Queries12", "Assets", {"Name"}, {"Assets.Name"}),
    #"Renamed Columns10" = Table.RenameColumns(#"Expanded Assets9",{{"Assets.Name", "Level4Name"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns10", each true),
    #"Merged Queries13" = Table.NestedJoin(#"Filtered Rows1", {"Level4Id"}, AssetTree, {"ChildAssetId"}, "AssetTree", JoinKind.LeftOuter),
    #"Expanded AssetTree3" = Table.ExpandTableColumn(#"Merged Queries13", "AssetTree", {"ParentAssetId"}, {"AssetTree.ParentAssetId"}),
    #"Renamed Columns11" = Table.RenameColumns(#"Expanded AssetTree3",{{"AssetTree.ParentAssetId", "Level5Id"}}),
    #"Merged Queries14" = Table.NestedJoin(#"Renamed Columns11", {"Level5Id"}, Assets, {"AssetId"}, "Assets", JoinKind.LeftOuter),
    #"Expanded Assets10" = Table.ExpandTableColumn(#"Merged Queries14", "Assets", {"Class"}, {"Assets.Class"}),
    #"Renamed Columns12" = Table.RenameColumns(#"Expanded Assets10",{{"Assets.Class", "Level5Class"}}),
    #"Merged Queries15" = Table.NestedJoin(#"Renamed Columns12", {"Level5Id"}, Assets, {"AssetId"}, "Assets", JoinKind.LeftOuter),
    #"Expanded Assets11" = Table.ExpandTableColumn(#"Merged Queries15", "Assets", {"Name"}, {"Assets.Name"}),
    #"Renamed Columns13" = Table.RenameColumns(#"Expanded Assets11",{{"Assets.Name", "Level5Name"}}),
    #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns13", each true),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows2", "Custom", each if [Level0Class] = "IfcSpace" then [ChildAssetId] else if [Level1Class] = "IfcSpace" then [ParentAssetId] else if [Level2Class] = "IfcSpace" then [Level2Id] else if [Level3Class] = "IfcSpace" then [Level3Id] else if [Level4Class] = "IfcSpace" then [Level4Id] else if [Level5Class] = "IfcSpace" then [Level5Id] else "unrelated to space"),
    #"Renamed Columns14" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "IfcSpace"}}),
    #"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns14", "Custom", each if [Level0Class] = "IfcBuildingStorey" then [Level0Name] else if [Level1Class] = "IfcBuildingStorey" then [Level1Name] else if [Level2Class] = "IfcBuildingStorey" then [Level2Name] else if [Level3Class] = "IfcBuildingStorey" then [Level3Name] else if [Level4Class] = "IfcBuildingStorey" then [Level4Name] else if [Level5Class] = "IfcBuildingStorey" then [Level5Name] else "unrelated to building storey"),
    #"Renamed Columns15" = Table.RenameColumns(#"Added Conditional Column1",{{"Custom", "IfcBuildingStorey"}}),
    #"Added Conditional Column2" = Table.AddColumn(#"Renamed Columns15", "Custom", each if [Level0Class] = "IfcSite" then [Level0Name] else if [Level1Class] = "IfcSite" then [Level1Name] else if [Level2Class] = "IfcSite" then [Level2Name] else if [Level3Class] = "IfcSite" then [Level3Name] else if [Level4Class] = "IfcSite" then [Level4Name] else if [Level5Class] = "IfcSite" then [Level5Name] else "unrelated to site"),
    #"Renamed Columns17" = Table.RenameColumns(#"Added Conditional Column2",{{"Custom", "IfcSite"}})
in
    #"Renamed Columns17"

Paste query

Click on done

To finish, rename the query

• Right click on Assets (2)
• Rename Assets (2) by AssetTreeSpatial

Close and apply your modification in Power Query

Now go to the Model part in Microsoft Power BI ( front part)

Click on new

Set up relation

• Select the table “Assets” with the first dropdown menu
• Select the colomn “AssetId” in the “Assets” table for this relation ( grey = selected)
• Select the second table “AssetTreeSpatial” with the second dropdown menu
• Select the colomn “ChildAssetId” in the “AssetTreeSpatial” table ( grey = selected)

Then

• Select Cardinality with “One to many (1:*)”
• “both” for “Cross filter direction”

Finally, click on OK

Check that the relation is active

If you liked this article, then please subscribe to our Youtube channel for video tutorials. You can also find us on Linked-in.