Need to create sankey chart in power bi but data is not structured

[Shahashesh]

Hi All,

Need to create Sankey chart but data is not structured. I have tried to do tranformations in power query and apply below solution to create pathnodes but query is not getting loaded in Power bi. 

Solution applied: Parent-Child Hierarchies with multiple parents in Power BI with Power Query (thebiccountant.com)

can anyone please help me with quick and easy solution if possible. Thanks.

[kamran_hassan]

Any success on this?
I have also been trying to create a Sankey Diagram up to 5 levels in Power BI on unstructured data but no luck so far.
I found a third party tool (Sankey Diagram for Power BI by ChartExpo) that supports 8 level Sankey Diagram but I am also stuck on un-structured to structured data conversion.
Your help would be appreciated.

Best Regards,

Kamran

[Shahashesh]

Hi @kamran_hassan ,

Solution provided in the same post by @v-jianboli-msft will help you in creating as many levels you have in your data but if more levels gets added in future then you have to modify the measure given in the same post. I am not sure how is your data if you can share the sample then i can look into it. Please check if this solution works for you or not. Thanks!

[v-jianboli-msft]

Hi @Shahashesh ,

This question is beyond the topic of the post, please consider tagging the answer and closing the thread. You may consider opening a new thread with the background of the calculation and the expected output. Thanks in advance!

Reference:

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft]

Hi @Shahashesh ,

Please try:

Measure = 
VAR _a =
    SELECTCOLUMNS ( 'Table 2', "Source", [Source] )
VAR _b =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table' ), [Source] IN _a ),
        "Destination", [Destination]
    )
VAR _c =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table' ), [Source] IN _b ),
        "Destination", [Destination]
    )
VAR _d =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table' ), [Source] IN _c ),
        "Destination", [Destination]
    )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[Source] ),
        SWITCH (
            TRUE (),
            MAX ( 'Table'[Source] ) IN _a, 1,
            MAX ( 'Table'[Source] ) IN _b, 1,
            MAX ( 'Table'[Source] ) IN _c, 1,
            MAX ( 'Table'[Source] ) IN _d, 1,
            0
        ),
        1
    )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[letyrobueno]

Thank you @v-jianboli-msft  for this solution! It's brilliant! 👏 The things one can do using DAX! 

Using the same idea, I implemented the other way around so that no matter which node is selected we can show all paths related to it end-to-end, which was what I needed.

Measure 2 = 
VAR _a = SELECTCOLUMNS ( 'DB Objects', "Source", [Nodes] )
VAR _b =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table' ), [Source] IN _a ),
        "Destination", [Destination]
    )
VAR _c =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table' ), [Source] IN _b ),
        "Destination", [Destination]
    )
VAR _d =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table' ), [Source] IN _c ),
        "Destination", [Destination]
    )

VAR _a_target = SELECTCOLUMNS ( 'DB Objects', "Destination", [Nodes] )
VAR _b_target =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table' ), [Destination] IN _a_target ),
        "Source", [Source]
    )
VAR _c_target =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table' ), [Destination] IN _b_target ),
        "Source", [Source]
    )
VAR _d_target =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table' ), [Destination] IN _c_target ),
        "Source", [Source]
    )

RETURN
    IF (
        ISFILTERED ('DB Objects'[Nodes]),
        SWITCH (
            TRUE (),
            MAX ( 'Table'[Source] ) IN _a || MAX ( 'Table'[Destination] ) IN _a_target, 1,
            MAX ( 'Table'[Source] ) IN _b || MAX ( 'Table'[Destination] ) IN _b_target, 1,
            MAX ( 'Table'[Source] ) IN _c || MAX ( 'Table'[Destination] ) IN _c_target, 1,
            MAX ( 'Table'[Source] ) IN _d || MAX ( 'Table'[Destination] ) IN _d_target, 1,
            0
        ),
        1
    )

[v-jianboli-msft]

Hi @Shahashesh ,

Sorry, I misunderstood before.

Please try:

First,create a new table for slicer:

Then create a measure and apply it to the filter

Measure =
VAR _a =
    SELECTCOLUMNS ( 'Table', "Source", [Source] )
VAR _b =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table (2)' ), [Source] IN _a ),
        "Destination", [Destination]
    )
VAR _c =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table (2)' ), [Source] IN _b ),
        "Destination", [Destination]
    )
RETURN
    IF (
        ISFILTERED ( 'Table'[Source] ),
        SWITCH (
            TRUE (),
            MAX ( 'Table (2)'[Source] ) IN _a, 1,
            MAX ( 'Table (2)'[Source] ) IN _b, 1,
            MAX ( 'Table (2)'[Source] ) IN _c, 1,
            0
        ),
        1
    )

Note: If you have more than 3 level Destination, you may need to update the measure(add more variables and add them to the switch function).

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Shahashesh]

Hi @v-jianboli-msft 

The solution which you provided is working fine but wanted to check this solution will work till how many levels gets added in the data. Secondly is there any way to do it dynamically where there is less or no manual intervention while refresh or more data gets added to it from future perspective. If yes, then can you please help how i can do it dynamically. Thanks!

[Shahashesh · Accepted solution]

Hi @v-jianboli-msft 

Also if the data is recursive then how we can dynamically solve it. Thanks!

[Shahashesh]

Hi @v-jianboli-msft 

Thank you so much for helping me, its perfectly working but one thing still it is missing it.

It is giving me all the levels but not giving Destination only for "ELE1661'  in sankey chart. I have tried to add one more variable as per your instructions but it is still not showcasing destination for ELE1661 only. Providing you snapshot of Data and sankey. Only this thing is left then i can mark it as a solution. 

[v-jianboli-msft]

Hi @Shahashesh ,

It works fine in my side, I can't reproduce your problem. 

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shahashesh]

Hi @v-jianboli-msft ,

Apologies for the hassle. Actually requirement is to show all levels whether it is directly or indirectly connected.

for E.g If i select "KPI0042" in my slicer then sanky should provide output as  below and 2nd level and 3rd level destination will be indirect linked to "KPI0042". I hope i make you understand exactly what i wanted to achieve it.

1st Path - All first level destination

2nd Path - 2nd level Destination

3rd Path - 3rd level Destination

[v-jianboli-msft]

Hi @Shahashesh ,

Please try:

First, create a new table for slicer:

Then create a new measure:

Flag =
VAR _a =
    SELECTCOLUMNS ( 'Table 2', "destination", [Destination] )
VAR _b =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table' ), [Destination] IN _a ),
        "source", [Source]
    )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[Destination] ),
        IF (
            MAX ( 'Table'[Destination] )
                IN SELECTCOLUMNS ( 'Table 2', "a", [Destination] )
                    || MAX ( 'Table'[Destination] ) IN _b,
            1,
            0
        ),
        0
    )

 Add the measure to the filter:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shahashesh]

Hi @v-jianboli-msft ,

Tried to implement it but it is not filtering the data once i select anything in my slicer.

However, requirement is to get all the levels as shown in my earlier snapshot and then MET0076 should get extended to ELE0012 as shown below. Can you please help me in achieving it. Thanks!

[v-jianboli-msft]

Hi @Shahashesh ,

Please try to create a new column Destination' for Destination:

Destination' = IF([Destination] in SELECTCOLUMNS('Source (Tabelle1)',"a",[Source]) , [Destination]&" ",[Destination])

Then apply it to the chart:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shahashesh]

Hi @v-jianboli-msft 

I have created column as you mentioned above then plug it into chart then i am getting below output

I am getting all the levels except "ELE0012" which is also linked to "MET0076" which is linked with my selection in slicer shown below in data snapshot. Is there any way to achieve this?

[v-jianboli-msft]

Hi @Shahashesh ,

Based on your sample picture, I have tried to achieve it in power bi.

Split Column by delimiter:

Here is the M code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-jianboli\Desktop\New Microsoft Excel Worksheet (2).xlsx"), null, false),
    Tabelle1_sheet = Source{[Item="Tabelle1",Kind="Sheet"]}[Data],
    FilterNullAndWhitespace = each List.Select(_, each _ <> null and (not (_ is text) or Text.Trim(_) <> "")),
    #"Removed Other Columns" = Table.SelectColumns(Tabelle1_sheet, List.Select(Table.ColumnNames(Tabelle1_sheet), each try not List.IsEmpty(FilterNullAndWhitespace(Table.Column(Tabelle1_sheet, _))) otherwise true)),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Source", type text}, {"Destination", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Destination", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Destination"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Destination", type text}})
in
    #"Changed Type2"

Here is the output:

Then apply it to the chart and create a slicer

Final output:

If the result is not what you want, please give more details to help me clarify it.

If you want to publish sample after removing sensitive data, please refer to: How to provide sample data in the Power BI Forum

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Shahashesh]

Hi @v-jianboli-msft 

Thank you for your solution but this is something which is coming at my end as well. Data is recursive and requirement is to get all the paths from source and destination wherever it is linked once we select any value in slicer.

I am able to achieve immediate level as you shown above but not the completely path in entire data.

Just wanted help in resolving this. Thanks!

[v-jianboli-msft]

HI @Shahashesh ,

What is the Column that  Destination comes from? What is the logic to transform it to Destination?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shahashesh]

Hi,

Destination column is the direct column coming from the source which is excel. I am splitting the destination column given in the image into rows using delimiter ",". it is giving me each row as a path for my source column given. Data has multiple sources for my destination and its endless loop.

basically i want to achieve that if i select any value in slicer it should give me all the paths in sankey wherever it is linked.

Now is it clear to you?

[Shahashesh]

Hi @v-jianboli-msft ,

I have files in my client environment so can you please tell how do it share pbix file