Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Network Diagram in Power BI

I am quite new to Power BI and I am trying to create a network diagram in it. I know it requires a source, a target and values as weight. Below is a sample data. Real data is similar but a bit different.

As you can see in the 'Test' column,

values C and G occur twice in keys 9 and 5.

values A and G occur twice in keys 7 and 11, but 11 also has F in it.

values F and G occur twice together in 11 and 13.


Key  Test
1       A
3       B
5       C
5       G
7       A
7      G
9      C
9      G
11    G
11    A
11    F
13    F
13   G

I want to create a table or a measure,


Test_1 Test_2 Weight
A         G         2
C          G        2
A          F         1 -- since key 11 also has F in it
G          F         2 -- since keys 11 and 13 have F and G in common


so that I can create a network diagram if possible

Need Help
  • dax's avatar
    dax
    6 years ago

    Hi Anonymous , 

    You also could refer to below M code to see whether it work or not

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjIGspzALFMgy1nHHcw2A8lD2ZZI4oYgje46jjpuEC5ItxtILhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [key = _t, Test = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Test", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Test.1", "Test.2", "Test.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"key", Int64.Type}, {"Test.1", type text}, {"Test.2", type text}, {"Test.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"key"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns", {"key"}, #"Unpivoted Columns", {"key"}, "Unpivoted Columns", JoinKind.LeftOuter),
    #"Expanded Unpivoted Columns" = Table.ExpandTableColumn(#"Merged Queries", "Unpivoted Columns", {"Value"}, {"Value.1"}),
    #"Grouped Rows" = Table.Group(#"Expanded Unpivoted Columns", {"Value", "Value.1"}, {{"Count", each Table.RowCount(_), type number}, {"all", each _, type table [key=number, Attribute=text, Value=text, Value.1=text]}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Value", "Value - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Value.1", "Value.1 - Copy"),
    #"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Value - Copy", "Value.1 - Copy"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns", {"Merged"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each Text.Combine(List.Sort(Text.Split([Merged],","),Order.Ascending),",")),
    #"Grouped Rows1" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each List.Average([Count]), type number}})
in
    #"Grouped Rows1"

    Best Regards,
    Zoe Zhi

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

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago
    Sorry, I am not understanding the logic of your expected output table, can you clarify? Why is A G 2 (that pair occurs once but A and G are also in other keys). Same for C G
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Greg_Deckler,

       

      Originally, the column 'Test' was a multi-valued attribute.

      It looked something like this:

       

      Key      Test 
1          A
3          B
5          C, G
7          A, G
9          C, G
11         G, A, F
13         F, G

      I split the test column using a delimiter and unpivoted the resulting columns which finally made Test column which I asked in the question.

      Through the network diagram, I wanted to show how many times every character is related to every other character, in the form of weight. 

      • dax's avatar
        dax
        Community Support
        6 years ago

        Hi Anonymous , 

        You also could refer to below M code to see whether it work or not

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjIGspzALFMgy1nHHcw2A8lD2ZZI4oYgje46jjpuEC5ItxtILhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [key = _t, Test = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Test", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Test.1", "Test.2", "Test.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"key", Int64.Type}, {"Test.1", type text}, {"Test.2", type text}, {"Test.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"key"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns", {"key"}, #"Unpivoted Columns", {"key"}, "Unpivoted Columns", JoinKind.LeftOuter),
    #"Expanded Unpivoted Columns" = Table.ExpandTableColumn(#"Merged Queries", "Unpivoted Columns", {"Value"}, {"Value.1"}),
    #"Grouped Rows" = Table.Group(#"Expanded Unpivoted Columns", {"Value", "Value.1"}, {{"Count", each Table.RowCount(_), type number}, {"all", each _, type table [key=number, Attribute=text, Value=text, Value.1=text]}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Value", "Value - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Value.1", "Value.1 - Copy"),
    #"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Value - Copy", "Value.1 - Copy"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns", {"Merged"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each Text.Combine(List.Sort(Text.Split([Merged],","),Order.Ascending),",")),
    #"Grouped Rows1" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each List.Average([Count]), type number}})
in
    #"Grouped Rows1"

        Best Regards,
        Zoe Zhi

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

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    You can use the below script to achieve this or please see the attached for details.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjIGspzALFMgyxnOcgezzOHqzOFilnB1lnAxQ0NUpiOC6QZhGqMygWpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Key = _t, Test = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"Test", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Key"}, #"Changed Type", {"Key"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Test"}, {"Test_2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Changed Type",{{"Test", "Test_1"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Test_1", "Test_2"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each not ([Test_1] = [Test_2]))
in
    #"Filtered Rows"

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn