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
jaryszek
Post Patron
Post Patron

If statement for multiple steps

Hi Guys,

 

my query is:

 

 

let
    TableName1 = "SAPMessageServersDef",
    TableName2 = "SAPMessageServersMap",
    table1 = fnGetTable(TableName1),
    table2 = fnGetTable(TableName2),
    tbl1= Table.UnpivotOtherColumns(table1, key, "Attributes", "Values"),
    tbl2= Table.UnpivotOtherColumns(table2, key, "Attributes", "Values"),
    key = Table.Column(fnTableKeys(TableName1), "Keys"){0},
    ColumnsToKeep = Table.Column(fnColumnsToKeep(TableName1), "List"){0},
    ColumnsOriginal = Table.ColumnNames(table1),
    ColumnsToRemove = List.Difference(ColumnsOriginal,ColumnsToKeep),
    DeploymentName = List.Contains(CheckIfDNameExists, TableName1),
     #"Source" = Table.NestedJoin(tbl1, List.Combine({key,{"Attributes"}}), tbl2, List.Combine({key,{"Attributes"}}), "TableMap", JoinKind.Inner),
    TopoTierRestricton = fnGetListColumnsTopoTier(table1),
    #"Expanded TableMap" = Table.ExpandTableColumn(#"Source", "TableMap", {"Values"}, {"TableMap.Values"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded TableMap", "MapValuesVn", each if Text.Contains([TableMap.Values], "Vn_Input_") or Text.Contains([TableMap.Values], "Vn_Comp_") then [Values] else [TableMap.Values], type text),
    TableMap_updated_0 = Table.Pivot(Table.RemoveColumns(#"Added Conditional Column",{"Values", "TableMap.Values"}), List.Distinct(#"Added Conditional Column"[Attributes]), "Attributes", "MapValuesVn"),
    TableMap_updated_1 = Table.TransformColumnTypes(TableMap_updated_0,fnGetListOfTypes (TableName1)),
    TableMap_updated_3 = Table.UnpivotOtherColumns(TableMap_updated_1, key, "Attributes", "MapValues"),
    #"Source2" = Table.NestedJoin(#"Added Conditional Column", List.Combine({key,{"Attributes"}}), TableMap_updated_3, List.Combine({key,{"Attributes"}}), "TableMap", JoinKind.Inner),
    #"Removed Columns2" = Table.RemoveColumns(Source2,{"TableMap.Values"}),
    #"Expanded TableMap1" = Table.ExpandTableColumn(#"Removed Columns2", "TableMap", {"MapValues"}, {"MapValues"}),

    //block to check if Topology is there, if yes:
    #"Merged Queries" = Table.NestedJoin(#"Expanded TableMap1", {"Topology"}, DeploymentNamesDiff, {"Topology"}, "DeploymentNamesDiff", JoinKind.LeftOuter),
    #"Expanded DeploymentNamesDiff" = Table.ExpandTableColumn(#"Merged Queries", "DeploymentNamesDiff", {"DeploymentName", "CheckIfDefEMap"}, {"DeploymentName", "CheckIfDefEMap"}),
    #"Added Custom" = Table.AddColumn(#"Expanded DeploymentNamesDiff", "CheckFirst", each if [Values] <> [MapValues] then 1 else 0),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Custom", "Check", each if [Values] <> [MapValuesVn] or [CheckIfDefEMap] = 1 then 1 else 0, type text),

    //block to check if Topology is there, if no:
    #"Added Conditional Column1_WT" = Table.AddColumn(#"Expanded TableMap1", "Check", each if [Values] <> [MapValuesVn] then 1 else 0, type text),

    #"AddColumnResult" = if List.Contains(Table.ColumnNames(#"Expanded TableMap1"),"Topology") = true then #"Added Conditional Column1" else #"Added Conditional Column1_WT",

    #"Filtered Rows" = Table.SelectRows(#"AddColumnResult", each ([Check] = 1)),
    #"Filter" = Table.NestedJoin(#"Added Conditional Column1", key, #"Filtered Rows", key, "Filter", JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(Filter,{"Values", "Check", "Filter", "CheckIfDefEMap", "CheckFirst", "MapValuesVn"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", List.Combine({key,{"DeploymentName", "Attributes"}})),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attributes]), "Attributes", "MapValues"),
    #"Pivoted Column End" = if Table.IsEmpty(#"Pivoted Column") then Table.AddColumn(Table.SelectRows(table1, each (key{0} = "")), "DeploymentName", each "") else #"Pivoted Column",
    #"Merged Queries0" = Table.NestedJoin(#"Pivoted Column End", TopoTierRestricton, TopoTierEnabled , TopoTierRestricton, "TopoTier", JoinKind.Inner),
    #"RemovedTopoTier" = Table.RemoveColumns(#"Merged Queries0",{"TopoTier"}),
    #"KeepColumnsFromCoreModel" = Table.RemoveColumns(#"RemovedTopoTier", ColumnsToRemove)
in
    #"KeepColumnsFromCoreModel"

 

 

 

Ok and you can see i have 2 blocks here:

//block to check if Topology is there, if yes:

 

and   //block to check if Topology is there, if no:

 

What i am trying to do is to check if in table is column "Topology" and if not skip whole merge operation (multiple steps) from "//block to check if Topology is there, if yes" :

#"AddColumnResult" = if List.Contains(Table.ColumnNames(#"Expanded TableMap1"),"Topology") = true then #"Added Conditional Column1" else #"Added Conditional Column1_WT",

 

But it is failing. 
In this step : 

#"Merged Queries" = Table.NestedJoin(#"Expanded TableMap1", {"Topology"}, DeploymentNamesDiff, {"Topology"}, "DeploymentNamesDiff", JoinKind.LeftOuter),

 

there is reference to "Topology" which does not exists. In this step i would to tell to Power Query to use only second part of code ("//block to check if Topology is there, if no:") instead of ("//block to check if Topology is there, if yes"). 

So what is the best practice to solve it? 

what to do with it? 

If i can add try otherwise statement for multiple steps i could avoid this error i suppose. 
But how to create proper if statement for multiple steps? 

Sorry i can not share with file because data is fragile. 
Please help,
Best Wishes,
Jacek

1 ACCEPTED SOLUTION
jennratten
Super User
Super User

Hello - I believe this will work.  Please try in your file and let me know.

 

I placed both blocks (block if yes and block if no) inside empty functions, created an evaluation statement to determine which block should be run, and a custom function to evaluate the appropriate block based on the result of the evaluation statement. 

 

Question: Should this line only be part of the block if yes?  It is referencing the last step in the that block.  This may be the source of your particular error, however, implementing a flow such as the one outlined below would be a better process in general.

    #"Filter" = Table.NestedJoin(#"Added Conditional Column1", key, #"Filtered Rows", key, "Filter", JoinKind.Inner),

 

The evaluation statement returns true/false:

    //evaluate - returns true/false
    #"AddColumnResult" = List.Contains(Table.ColumnNames(#"Expanded TableMap1"),"Topology"),

 

If the evaluation statement returns true, this block is run:

    //block to check if Topology is there, if yes:
    fnBlockIfYes = ( ) =>
    let 
    #"Merged Queries" = Table.NestedJoin(#"Expanded TableMap1", {"Topology"}, DeploymentNamesDiff, {"Topology"}, "DeploymentNamesDiff", JoinKind.LeftOuter),
    #"Expanded DeploymentNamesDiff" = Table.ExpandTableColumn(#"Merged Queries", "DeploymentNamesDiff", {"DeploymentName", "CheckIfDefEMap"}, {"DeploymentName", "CheckIfDefEMap"}),
    #"Added Custom" = Table.AddColumn(#"Expanded DeploymentNamesDiff", "CheckFirst", each if [Values] <> [MapValues] then 1 else 0),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Custom", "Check", each if [Values] <> [MapValuesVn] or [CheckIfDefEMap] = 1 then 1 else 0, type text)
    in #"Added Conditional Column1",

 

If the evaluation statement returns false, this block is run:

    //block to check if Topology is there, if no:
    fnBlockIfNo = ( ) =>
    let 
    #"Added Conditional Column1_WT" = Table.AddColumn(#"Expanded TableMap1", "Check", each if [Values] <> [MapValuesVn] then 1 else 0, type text)
    in #"Added Conditional Column1_WT",

 

Define a custom function to run the appropriate block based on the evaluation result, invoke the function, and use the result as the source for the next step.

    //define a custom function to return the appropriate result
    //The second and third function arguments do not include types since they are functions.
    fnGetResult = (evaluation as logical, resultIfTrue, resultIfFalse) => 
        if evaluation then resultIfTrue() else resultIfFalse(),
    //Invoke the function.
    FunctionInvoked = fnGetResult(#"AddColumnResult", fnBlockIfYes, fnBlockIfNo),

    // Change the table in the next step to FunctionInvoked.
    #"Filtered Rows" = Table.SelectRows(FunctionInvoked, each ([Check] = 1)),

 

The complete query. Note, the question above about the #"Filter" step needs to be addressed.  As it is currently, the query will likely error at that step.

let
    TableName1 = "SAPMessageServersDef",
    TableName2 = "SAPMessageServersMap",
    table1 = fnGetTable(TableName1),
    table2 = fnGetTable(TableName2),
    tbl1= Table.UnpivotOtherColumns(table1, key, "Attributes", "Values"),
    tbl2= Table.UnpivotOtherColumns(table2, key, "Attributes", "Values"),
    key = Table.Column(fnTableKeys(TableName1), "Keys"){0},
    ColumnsToKeep = Table.Column(fnColumnsToKeep(TableName1), "List"){0},
    ColumnsOriginal = Table.ColumnNames(table1),
    ColumnsToRemove = List.Difference(ColumnsOriginal,ColumnsToKeep),
    DeploymentName = List.Contains(CheckIfDNameExists, TableName1),
     #"Source" = Table.NestedJoin(tbl1, List.Combine({key,{"Attributes"}}), tbl2, List.Combine({key,{"Attributes"}}), "TableMap", JoinKind.Inner),
    TopoTierRestricton = fnGetListColumnsTopoTier(table1),
    #"Expanded TableMap" = Table.ExpandTableColumn(#"Source", "TableMap", {"Values"}, {"TableMap.Values"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded TableMap", "MapValuesVn", each if Text.Contains([TableMap.Values], "Vn_Input_") or Text.Contains([TableMap.Values], "Vn_Comp_") then [Values] else [TableMap.Values], type text),
    TableMap_updated_0 = Table.Pivot(Table.RemoveColumns(#"Added Conditional Column",{"Values", "TableMap.Values"}), List.Distinct(#"Added Conditional Column"[Attributes]), "Attributes", "MapValuesVn"),
    TableMap_updated_1 = Table.TransformColumnTypes(TableMap_updated_0,fnGetListOfTypes (TableName1)),
    TableMap_updated_3 = Table.UnpivotOtherColumns(TableMap_updated_1, key, "Attributes", "MapValues"),
    #"Source2" = Table.NestedJoin(#"Added Conditional Column", List.Combine({key,{"Attributes"}}), TableMap_updated_3, List.Combine({key,{"Attributes"}}), "TableMap", JoinKind.Inner),
    #"Removed Columns2" = Table.RemoveColumns(Source2,{"TableMap.Values"}),
    #"Expanded TableMap1" = Table.ExpandTableColumn(#"Removed Columns2", "TableMap", {"MapValues"}, {"MapValues"}),

    //block to check if Topology is there, if yes:
    fnBlockIfYes = ( ) =>
    let 
    #"Merged Queries" = Table.NestedJoin(#"Expanded TableMap1", {"Topology"}, DeploymentNamesDiff, {"Topology"}, "DeploymentNamesDiff", JoinKind.LeftOuter),
    #"Expanded DeploymentNamesDiff" = Table.ExpandTableColumn(#"Merged Queries", "DeploymentNamesDiff", {"DeploymentName", "CheckIfDefEMap"}, {"DeploymentName", "CheckIfDefEMap"}),
    #"Added Custom" = Table.AddColumn(#"Expanded DeploymentNamesDiff", "CheckFirst", each if [Values] <> [MapValues] then 1 else 0),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Custom", "Check", each if [Values] <> [MapValuesVn] or [CheckIfDefEMap] = 1 then 1 else 0, type text)
    in #"Added Conditional Column1",

    //block to check if Topology is there, if no:
    fnBlockIfNo = ( ) =>
    let 
    #"Added Conditional Column1_WT" = Table.AddColumn(#"Expanded TableMap1", "Check", each if [Values] <> [MapValuesVn] then 1 else 0, type text)
    in #"Added Conditional Column1_WT",

    //evaluate - returns true/false
    #"AddColumnResult" = List.Contains(Table.ColumnNames(#"Expanded TableMap1"),"Topology"),
    //#"AddColumnResult" = if List.Contains(Table.ColumnNames(#"Expanded TableMap1"),"Topology") = true then #"Added Conditional Column1" else #"Added Conditional Column1_WT",

    //define a custom function to return the appropriate result
    //The second and third function arguments do not include types since they are functions.
    fnGetResult = (evaluation as logical, resultIfTrue, resultIfFalse) => 
        if evaluation then resultIfTrue() else resultIfFalse(),
    //Invoke the function.
    FunctionInvoked = fnGetResult(#"AddColumnResult", fnBlockIfYes, fnBlockIfNo),

    // Change the table in the next step to FunctionInvoked.
    #"Filtered Rows" = Table.SelectRows(FunctionInvoked, each ([Check] = 1)),

    // Should this step be part of the Yes block?  It is referencing the last step of that block.
    #"Filter" = Table.NestedJoin(#"Added Conditional Column1", key, #"Filtered Rows", key, "Filter", JoinKind.Inner),

    #"Removed Columns" = Table.RemoveColumns(Filter,{"Values", "Check", "Filter", "CheckIfDefEMap", "CheckFirst", "MapValuesVn"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", List.Combine({key,{"DeploymentName", "Attributes"}})),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attributes]), "Attributes", "MapValues"),
    #"Pivoted Column End" = if Table.IsEmpty(#"Pivoted Column") then Table.AddColumn(Table.SelectRows(table1, each (key{0} = "")), "DeploymentName", each "") else #"Pivoted Column",
    #"Merged Queries0" = Table.NestedJoin(#"Pivoted Column End", TopoTierRestricton, TopoTierEnabled , TopoTierRestricton, "TopoTier", JoinKind.Inner),
    #"RemovedTopoTier" = Table.RemoveColumns(#"Merged Queries0",{"TopoTier"}),
    #"KeepColumnsFromCoreModel" = Table.RemoveColumns(#"RemovedTopoTier", ColumnsToRemove)
in
    #"KeepColumnsFromCoreModel"

 

 

View solution in original post

6 REPLIES 6
jaryszek
Post Patron
Post Patron

o wow seems complicated, thank you very much!

 

 

 // Should this step be part of the Yes block?  It is referencing the last step of that block.
    #"Filter" = Table.NestedJoin(#"Added Conditional Column1", key, #"Filtered Rows", key, "Filter", JoinKind.Inner),

 

It is step common for both of them, so it can be excluded outside. 

 

i do not catching this one:

 

 //The second and third function arguments do not include types since they are functions.
    fnGetResult = (evaluation as logical, resultIfTrue, resultIfFalse) => 
        if evaluation then resultIfTrue() else resultIfFalse(),
    //Invoke the function.
    FunctionInvoked = fnGetResult(#"AddColumnResult", fnBlockIfYes, fnBlockIfNo),

 

so if test #"AddColumnResult" is true it will trigger resultIfTrue() but where is refennce to this function? 

fnBlockIfYes has to be triggered somehow in next step but i do not know where it happens.

 

Best,
Jacek

 

 

You're welcome!  See the script included in the original reply marked "The complete query".  This has all of the steps and functions.  The fnBlockIfYes and fnBlockIfNo functions are at about the same location in the query where you had originally commented yes block and no block.

 

thank you. 

sorry for answering in bad place. 
Hmm but still how this fnBlockIfYes is triggered? 

 

This function is triggered: 

resultIfTrue() but not fnBlockIfYes , you mean this should be the same names? 

 

Best,
Jacek

Even though we define fnBlockIfYes and fnBlockIfNo further up in the script, it is only triggered by invoking fnGetResult further down.  The custom function essentially says:

if #"AddColumnResult" then fnBlockIfYes() else fnBlockIfNo()

 

Here is how it breaks down...

 

Define the inputs/arguments of the custom function named fnGetResults (evaluation, resultIfTrue, resultIfFalse).  

 

fnGetResult = (evaluation as logical, resultIfTrue, resultIfFalse) =>

 

Define what happens when the function is invoked.

When the inputs were defined above, there were no types included for the 2nd and 3rd arguments.  This is because they are placeholders for functions.  When we define what happens upon invokation, we add the () to indicate it is a function.

if evaluation          // #"AddColumnResult" (true/false)
then resultIfTrue()    // fnBlockIfYes()
else resultIfFalse(),  // fnBlockIfNo()


Invoke the custom function.

To invoke the custom function, you just list the name of the function and then the objects/values that should be used for each argument.

FunctionInvoked = fnGetResult(#"AddColumnResult", fnBlockIfYes, fnBlockIfNo),
// in other words...
// if #"AddColumnResult" then fnBlockIfYes() else fnBlockIfNo()

 

Does that make sense?

Thank you so much!

 

Yes i got it, it is clear! 

Best wishes,
Jacek

jennratten
Super User
Super User

Hello - I believe this will work.  Please try in your file and let me know.

 

I placed both blocks (block if yes and block if no) inside empty functions, created an evaluation statement to determine which block should be run, and a custom function to evaluate the appropriate block based on the result of the evaluation statement. 

 

Question: Should this line only be part of the block if yes?  It is referencing the last step in the that block.  This may be the source of your particular error, however, implementing a flow such as the one outlined below would be a better process in general.

    #"Filter" = Table.NestedJoin(#"Added Conditional Column1", key, #"Filtered Rows", key, "Filter", JoinKind.Inner),

 

The evaluation statement returns true/false:

    //evaluate - returns true/false
    #"AddColumnResult" = List.Contains(Table.ColumnNames(#"Expanded TableMap1"),"Topology"),

 

If the evaluation statement returns true, this block is run:

    //block to check if Topology is there, if yes:
    fnBlockIfYes = ( ) =>
    let 
    #"Merged Queries" = Table.NestedJoin(#"Expanded TableMap1", {"Topology"}, DeploymentNamesDiff, {"Topology"}, "DeploymentNamesDiff", JoinKind.LeftOuter),
    #"Expanded DeploymentNamesDiff" = Table.ExpandTableColumn(#"Merged Queries", "DeploymentNamesDiff", {"DeploymentName", "CheckIfDefEMap"}, {"DeploymentName", "CheckIfDefEMap"}),
    #"Added Custom" = Table.AddColumn(#"Expanded DeploymentNamesDiff", "CheckFirst", each if [Values] <> [MapValues] then 1 else 0),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Custom", "Check", each if [Values] <> [MapValuesVn] or [CheckIfDefEMap] = 1 then 1 else 0, type text)
    in #"Added Conditional Column1",

 

If the evaluation statement returns false, this block is run:

    //block to check if Topology is there, if no:
    fnBlockIfNo = ( ) =>
    let 
    #"Added Conditional Column1_WT" = Table.AddColumn(#"Expanded TableMap1", "Check", each if [Values] <> [MapValuesVn] then 1 else 0, type text)
    in #"Added Conditional Column1_WT",

 

Define a custom function to run the appropriate block based on the evaluation result, invoke the function, and use the result as the source for the next step.

    //define a custom function to return the appropriate result
    //The second and third function arguments do not include types since they are functions.
    fnGetResult = (evaluation as logical, resultIfTrue, resultIfFalse) => 
        if evaluation then resultIfTrue() else resultIfFalse(),
    //Invoke the function.
    FunctionInvoked = fnGetResult(#"AddColumnResult", fnBlockIfYes, fnBlockIfNo),

    // Change the table in the next step to FunctionInvoked.
    #"Filtered Rows" = Table.SelectRows(FunctionInvoked, each ([Check] = 1)),

 

The complete query. Note, the question above about the #"Filter" step needs to be addressed.  As it is currently, the query will likely error at that step.

let
    TableName1 = "SAPMessageServersDef",
    TableName2 = "SAPMessageServersMap",
    table1 = fnGetTable(TableName1),
    table2 = fnGetTable(TableName2),
    tbl1= Table.UnpivotOtherColumns(table1, key, "Attributes", "Values"),
    tbl2= Table.UnpivotOtherColumns(table2, key, "Attributes", "Values"),
    key = Table.Column(fnTableKeys(TableName1), "Keys"){0},
    ColumnsToKeep = Table.Column(fnColumnsToKeep(TableName1), "List"){0},
    ColumnsOriginal = Table.ColumnNames(table1),
    ColumnsToRemove = List.Difference(ColumnsOriginal,ColumnsToKeep),
    DeploymentName = List.Contains(CheckIfDNameExists, TableName1),
     #"Source" = Table.NestedJoin(tbl1, List.Combine({key,{"Attributes"}}), tbl2, List.Combine({key,{"Attributes"}}), "TableMap", JoinKind.Inner),
    TopoTierRestricton = fnGetListColumnsTopoTier(table1),
    #"Expanded TableMap" = Table.ExpandTableColumn(#"Source", "TableMap", {"Values"}, {"TableMap.Values"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded TableMap", "MapValuesVn", each if Text.Contains([TableMap.Values], "Vn_Input_") or Text.Contains([TableMap.Values], "Vn_Comp_") then [Values] else [TableMap.Values], type text),
    TableMap_updated_0 = Table.Pivot(Table.RemoveColumns(#"Added Conditional Column",{"Values", "TableMap.Values"}), List.Distinct(#"Added Conditional Column"[Attributes]), "Attributes", "MapValuesVn"),
    TableMap_updated_1 = Table.TransformColumnTypes(TableMap_updated_0,fnGetListOfTypes (TableName1)),
    TableMap_updated_3 = Table.UnpivotOtherColumns(TableMap_updated_1, key, "Attributes", "MapValues"),
    #"Source2" = Table.NestedJoin(#"Added Conditional Column", List.Combine({key,{"Attributes"}}), TableMap_updated_3, List.Combine({key,{"Attributes"}}), "TableMap", JoinKind.Inner),
    #"Removed Columns2" = Table.RemoveColumns(Source2,{"TableMap.Values"}),
    #"Expanded TableMap1" = Table.ExpandTableColumn(#"Removed Columns2", "TableMap", {"MapValues"}, {"MapValues"}),

    //block to check if Topology is there, if yes:
    fnBlockIfYes = ( ) =>
    let 
    #"Merged Queries" = Table.NestedJoin(#"Expanded TableMap1", {"Topology"}, DeploymentNamesDiff, {"Topology"}, "DeploymentNamesDiff", JoinKind.LeftOuter),
    #"Expanded DeploymentNamesDiff" = Table.ExpandTableColumn(#"Merged Queries", "DeploymentNamesDiff", {"DeploymentName", "CheckIfDefEMap"}, {"DeploymentName", "CheckIfDefEMap"}),
    #"Added Custom" = Table.AddColumn(#"Expanded DeploymentNamesDiff", "CheckFirst", each if [Values] <> [MapValues] then 1 else 0),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Custom", "Check", each if [Values] <> [MapValuesVn] or [CheckIfDefEMap] = 1 then 1 else 0, type text)
    in #"Added Conditional Column1",

    //block to check if Topology is there, if no:
    fnBlockIfNo = ( ) =>
    let 
    #"Added Conditional Column1_WT" = Table.AddColumn(#"Expanded TableMap1", "Check", each if [Values] <> [MapValuesVn] then 1 else 0, type text)
    in #"Added Conditional Column1_WT",

    //evaluate - returns true/false
    #"AddColumnResult" = List.Contains(Table.ColumnNames(#"Expanded TableMap1"),"Topology"),
    //#"AddColumnResult" = if List.Contains(Table.ColumnNames(#"Expanded TableMap1"),"Topology") = true then #"Added Conditional Column1" else #"Added Conditional Column1_WT",

    //define a custom function to return the appropriate result
    //The second and third function arguments do not include types since they are functions.
    fnGetResult = (evaluation as logical, resultIfTrue, resultIfFalse) => 
        if evaluation then resultIfTrue() else resultIfFalse(),
    //Invoke the function.
    FunctionInvoked = fnGetResult(#"AddColumnResult", fnBlockIfYes, fnBlockIfNo),

    // Change the table in the next step to FunctionInvoked.
    #"Filtered Rows" = Table.SelectRows(FunctionInvoked, each ([Check] = 1)),

    // Should this step be part of the Yes block?  It is referencing the last step of that block.
    #"Filter" = Table.NestedJoin(#"Added Conditional Column1", key, #"Filtered Rows", key, "Filter", JoinKind.Inner),

    #"Removed Columns" = Table.RemoveColumns(Filter,{"Values", "Check", "Filter", "CheckIfDefEMap", "CheckFirst", "MapValuesVn"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", List.Combine({key,{"DeploymentName", "Attributes"}})),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attributes]), "Attributes", "MapValues"),
    #"Pivoted Column End" = if Table.IsEmpty(#"Pivoted Column") then Table.AddColumn(Table.SelectRows(table1, each (key{0} = "")), "DeploymentName", each "") else #"Pivoted Column",
    #"Merged Queries0" = Table.NestedJoin(#"Pivoted Column End", TopoTierRestricton, TopoTierEnabled , TopoTierRestricton, "TopoTier", JoinKind.Inner),
    #"RemovedTopoTier" = Table.RemoveColumns(#"Merged Queries0",{"TopoTier"}),
    #"KeepColumnsFromCoreModel" = Table.RemoveColumns(#"RemovedTopoTier", ColumnsToRemove)
in
    #"KeepColumnsFromCoreModel"

 

 

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.

Top Solution Authors
Top Kudoed Authors