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 differ...
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.

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity 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