Show query dependencies for all merged queries

[Anonymous]

I have a datasource called AuditLog. I have two queries that reference AuditLog.

1. The query EscalationsPerTicket takes the AuditLog data and manipulates it.
2. The query IndividualEscalations takes the AuditLog data, manipulates it, and merges the result with the query EscalationsPerTicket.

This is what I expected to see in query dependencies: 

• An arrow from AuditLog to EscalationsPerTicket
• An arrow from AuditLog to IndividualEscalations
• An arrow from EscalationsPerTicket to IndividualEscalations

Instead I see this, as shown in the below screenshot:

• An arrow from AuditLog to EscalationsPerTicket
• An arrow from EscalationsPerTicket to IndividualEscalations

I believe the image is misleading as it implies IndividualEscalations only depends on the EscalationsPerTicket query. Here are the relevant queries:

AuditLog query:

let
    Source = Excel.Workbook(File.Contents("<omitted>.xlsx"), null, true),
    IncidentsRequests_Sheet = Source{[Item="IncidentsRequests",Kind="Sheet"]}[Data],
    #"Removed header rows" = Table.Skip(IncidentsRequests_Sheet,2),
    #"Promoted headers" = Table.PromoteHeaders(#"Removed header rows", [PromoteAllScalars=true]),
    #"Changed column types" = <omitted>,
    #"Added column [Index]" = Table.AddIndexColumn(#"Changed column types", "Index", 1, 1),
    #"Sorted by descending [Index]" = Table.Sort(#"Added column [Index]",{{"Index", Order.Descending}}),
    #"Removed footer rows" = Table.Skip(#"Sorted by descending [Index]",2),
    #"Removed column [Index]" = Table.RemoveColumns(#"Removed footer rows",{"Index"})
in
    #"Removed column [Index]"

EscalationsPerTicket query:

let
    Source = #"20171101-02_AuditLog",
    #"Added column IsEscalation" = Table.AddColumn(Source, "IsEscalation", each if <omitted> = "Escalation Log" then 1 else 0),
    #"Changed IsEscalation to Int" = Table.TransformColumnTypes(#"Added column IsEscalation",{{"IsEscalation", Int64.Type}}),
    #"Grouped rows by Incident ID, summed IsEscalation" = Table.Group(#"Changed IsEscalation to Int", {"Incident ID"}, {{"TotalEscalations", each List.Sum([IsEscalation]), type number}})
in
    #"Grouped rows by Incident ID, summed IsEscalation"

IndividualEscalations query:

let
    Source = #"20171101-02_AuditLog",
    #"Filtered out non-escalation rows" = Table.SelectRows(Source, each ([<omitted>] = "Escalation Log")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered out non-escalation rows",{"<omitted>", "<omitted>"}),
    #"Removed redundant info 1" = Table.ReplaceValue(#"Removed Columns","<omitted> ","",Replacer.ReplaceText,{"<omitted>"}),
    #"Removed redundant info 2" = Table.ReplaceValue(#"Removed redundant info 1","<omitted> ","",Replacer.ReplaceText,{"<omitted>"}),
    #"Removed redundant info 3" = Table.ReplaceValue(#"Removed redundant info 2","<omitted>","",Replacer.ReplaceText,{"<omitted>"}),
    #"Split off OriginalTeam" = Table.SplitColumn(#"Removed redundant info 3", "<omitted>", Splitter.SplitTextByEachDelimiter({"<omitted>"}, QuoteStyle.Csv, false), {"<omitted>"}),
    #"Split off EscalatedTeam" = Table.SplitColumn(#"Split off OriginalTeam", "<omitted>", Splitter.SplitTextByEachDelimiter({"<omitted>"}, QuoteStyle.Csv, true), {"<omitted>"}),
    #"Removed redundant column" = Table.RemoveColumns(#"Split off EscalatedTeam",{"<omitted>"}),
    #"Merged with EscalationsPerTicket" = Table.NestedJoin(#"Removed redundant column",{"Incident ID"},EscalationsPerTicket,{"Incident ID"},"EscalationsPerTicket",JoinKind.LeftOuter),
    #"Expanded EscalationsPerTicket" = Table.ExpandTableColumn(#"Merged with EscalationsPerTicket", "EscalationsPerTicket", {"Escalations"}, {"Escalations"}),
    #"Renamed columns" = Table.RenameColumns(#"Expanded EscalationsPerTicket",{{"Escalations", "TotalEscalations"}, {"<omitted>", "OriginalTeam"}, {"<omitted>", "EscalatedTeam"}})
in
    #"Renamed columns"

[v-qiuyu-msft]

Hi @Anonymous,

The data model which you expected is not supported, it will cause ambiguity. You can set Cross filter direction as Both. Please take a look at those two links: 

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

https://community.powerbi.com/t5/Desktop/Data-model-loop-cant-point-2-transaction-dates-to-DateTable/td-p/97261

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft, thanks for your reply.

These transformations are in Power Query. There is no filtering direction here, I can only join tables.

This is a sample image of a join aka a merge in Power Query:

By the time the data is finished in Power Query and loaded into the data model, I only have one table. I have no joins in the "Relationships" tab of Power BI Desktop.

[v-qiuyu-msft]

Hi @Anonymous,

Do you mean there is only one table loaded into the data model? I'm a little confused now. 

From your original question, you said there were three tables: AuditLog, EscalationsPerTicket and IndividualEscalations, and you want to create relationships among each other. This is not supported. 

The screenshot posted in my previous reply is from Manage Relationships. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft,

This is what I refer to as Power Query:

I can see these tables in Power Query. I can reference one query to another to copy all its contents. I can also add a query step within a query, to merge its data with another query's data.

When I'm done in Power Query, I click the Close & Apply option in Power Query. The window closes, and I can select the different tabs of Power BI Desktop.

What I refer to as the Data Model is the "Relationships" tab on the bottom left. As discussed, there is no join here. Therefore the cross filter direction does not apply to my issue.

There are 3 tables (AuditLog, EscalationsPerTicket and IndividualEscalations) in Power Query. But after they are joined in Power Query, they are one table. That one table exists by itself in the Data Model and doesn't get joined to anything.