Formatting data for Sankey Visual

[SuchCT]

Hello,

I'm trying to show data in a Sankey Visual. It should recieve Source, Destination and Weight. The data is a structured in a way that makes it difficult right now. Does anyone know a way to turn it from this into something more "Sankey friendly"?

Order ID    Item      Sent       Returned   OK     NOK

A	1	444	195	138	58
A	2	934	176	80	96
A	3	569	155	149	6
A	4	509	218	87	132
B	1	891	17	16	1
B	2	252	137	14	123
C	1	424	24	1	23
C	2	812	709	113	596
D	1	323	140	32	107
E	1	533	192	45	146

I´m adding an example for order A on a format that would work but I don't know how to achieve it

OrderID     Item   Source       Destination      Weight

A	1	Sent	Returned	195
A	1	Sent	Not Returned	249
A	1	Returned	OK	138
A	1	Returned	NOK	58
A	2	Sent	Returned	176
A	2	Sent	Not Returned	758
A	2	Returned	OK	80
A	2	Returned	NOK	96
A	3	Sent	Returned	155
A	3	Sent	Not Returned	414
A	3	Returned	OK	149
A	3	Returned	NOK	6
A	4	Sent	Returned	218
A	4	Sent	Not Returned	291
A	4	Returned	OK	87
A	4	Returned	NOK	132

Thanks in advance

[Syndicate_Admin]

Hello, reading the post and trying to put the breakdown of an income statement in zankey, I have the doubt of how I do to make the data appear and even make the link wider.

Thanks a lot.

[lbendlin · Accepted solution]

What story are you trying to tell?  Percentage of erroneously returned items?

Sankey is not the right chart for this.  Try a funnel chart.

But if you insist - here's the Power Query to convert your data into Sankey format.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZU9JDsIwDPwKyrmHeMtyhMIrqv7/G2SGFChEijWZxXa2LV0vfyctSX4Jdz8bepzf1g4YLe3LaDygjtvNodcyassgyls32EuHHoHqwB8dycjgVBrylaOUhhswyC4cgFLIHSrGaygzlLmJGg3rjLuCZcHzS0WwCWrlDiLcd+5/nwFTY+tMDJgrDY9pCKOhQ/PXL0eH/Qk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order ID" = _t, Item = _t, Sent = _t, Returned = _t, OK = _t, NOK = _t]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Order ID", "Item", "Sent", "Returned", "OK"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Sent", Int64.Type}, {"Returned", Int64.Type}, {"OK", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Source", each "Sent"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Destination", each "Returned"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Order ID", "Item", "Source", "Destination", "Returned", "Sent", "OK"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Returned", "Weight"}}),
    SentReturned = Table.SelectColumns(#"Renamed Columns",{"Order ID", "Item", "Source", "Destination", "Weight"}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Destination", each "Not Returned"),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Weight", each [Sent]-[Returned]),
    SentNotReturned = Table.SelectColumns(#"Added Custom3",{"Order ID", "Item", "Source", "Destination", "Weight"}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type", "Source", each "Returned"),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Destination", each "OK"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom5",{"Order ID", "Item", "Sent", "Returned", "Source", "Destination", "OK"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"OK", "Weight"}}),
    ReturnedOK = Table.SelectColumns(#"Renamed Columns1",{"Order ID", "Item", "Source", "Destination", "Weight"}),
    #"Added Custom6" = Table.AddColumn(#"Added Custom4", "Destination", each "NOK"),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "Weight", each [Returned]-[OK]),
    ReturnedNOK = Table.SelectColumns(#"Added Custom7",{"Order ID", "Item", "Source", "Destination", "Weight"})
in
    SentReturned & SentNotReturned & ReturnedOK & ReturnedNOK

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[SuchCT]

Thanks for your answer, it seems like that would be the answer. However the data in this post was just an example, what I have is a Power BI Table that has several columns, including the ones that are used in this graph. How could one adapt your code to work for that?

I think that Sankey is the correct graph because what I have to do is show from all the sent items where did each one go they could be returned or not, and the ones that are returned can have multiple explanations.

[lbendlin]

I'm still not convinced that this tells a good story

My code should work regardless of any other columns that you may have.

[SuchCT]

Thank you so much, clearly that's the answer. 
I'm still not sure how to apply that code to take the columns form my BI instead of the sample data but I´ll figure it out.