Relations between Incidents and Changes

[Cypher294]

Hello ,

I've got a question regarding some ITSM reporting

we have a list of incidents and some of them are caused by RFCs.

We've got a relation between the Master Incident Number and the fact if it was caused by an RFC.

Now we want to know the total number of Incidents which were caused by rfc including the Slave Incident to masters which have an relation to the rfcs. The slaves just "know" the number of the master incident not of the rfcs which caused the master:

All of the information is stored within one table.

Best way for me is to mark the incidents within an addtitional column " caused by RFC 2" which will be set to 1 if the incident itselfd was caused by RFC ( master itself ) or if the incident is a slave and the Master was caused by an RFC all other Incidents will get a 0

Example:

[MarcelBeug]

In Power Query you can merge the table with itself to get the master incident data on the same row as the incident data.

If either of the incident or its master is caused by an RfC, the new column gets value 1, else 0.

Beware of case sensitivity in Power Query. I changed all variations of "Yes", "yes" and "NO" to uppercase (in another query that loaded Table1 into Power Query).

let
    Source = Table1,
    #"Merged Queries" = Table.NestedJoin(Source,{"Master Incident Number"},Table1,{"Incident Nr"},"Master",JoinKind.LeftOuter),
    #"Expanded Master" = Table.ExpandTableColumn(#"Merged Queries", "Master", {"Caused By Change"}, {"Master.Caused By Change"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Master", "caused by RFC 2", each if [Caused By Change] = "YES" or [Master.Caused By Change] = "YES" then 1 else 0, Int64.Type)
in
    #"Added Custom"

[Cypher294]

Hi Marcel,

thanks for your feedback.

Generally it works but I have the problem now, that after inserting the code to my dataset the already created conditional columns etc. which are necessary for other calculations will be ignored or deleted from my table.

There are no errors or something like that but all already set up calculated columns are away and just the original columns from the root database are visible. And it does not matter on which position of the code I am inserting your code.

If I put it before my code/calculations it works until the point of the "old" calculations start from this point on the merged quirie is ignored and no longer existing ( marked with red )  .

[MarcelBeug · Accepted solution]

Probably you didn't refer to your previous step in #"Merged Queries". It should be like:

    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns1",{"Master Incident Number"},#"Renamed Columns1",{"Incident Nr"},"Master",JoinKind.LeftOuter),

[Cypher294]

thanks, that was the missing hint 🙂

Amazing. Thank You