Line Chart with Multi-Output Data

[Anonymous]

Hello.

I'm looking to create a line chart with opportunity close date on the x-axis and win % on the y-axis. I am then looking to have 3 separate lines showing the win % by product over time. The complexity is that multiple products can be associated with a single opportunity and are listed in a single column of the data source. I want the lines in the chart to track the win % of a given product regardless of the other products associated with that opportunity - one line for product A, one line for B, and one line for C (so some opportunities will be counted more than once and contribute to the lines of more than one product). A hyper-simplified version of the data is shown below. Is this possible? Perhaps with some sort of nested if/ contains text calculation? 

Opportunity ID	Date Closed	Won?	Products
1	1/2/2018	Won	A
2	3/16/2018	Won	B, C
3	4/30/2019	Won	C, A
4	10/27/2019	Lost	B, C, A
5	2/2/2020	Won	C
6	2/7/2020	Lost	C, B
7	4/15/2020	Lost	A, B
8	6/7/2020	Won	A
9	12/13/2020	Won	A, B, C
10	1/5/2021	Lost	A

Any help would be greatly appreciated. 

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

First, you need to transform your table to below:

Please refer to the M query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZC7DsIwDEV/JcocKXHSB0yojwUJCTaGqgOiHZBQU7XpwN/juA0tXZLYx/faTlXxa9/bwU3dy33YueSClw/XsuJtx7bB6G67E163wTbT0428FhUHTIDUUis4zCV4ZoQ0voyE5J/lghWEDQaRNMrj4w8Xgs3qyBsjTAO/2NEt+lATY6ypuVarA6GEUBrQIkZlTjil5hDveBa4nzdZ5du1/CygJZgdRGlYDRT9CrnDxp3X9Rc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Opportunity ID", Int64.Type}, {"Date Closed", type date}, {"Won?", type text}, {"Products", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Products", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Products.1", "Products.2", "Products.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Products.1", type text}, {"Products.2", type text}, {"Products.3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Opportunity ID", "Date Closed", "Won?"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns"," ","",Replacer.ReplaceText,{"Value"})
in
    #"Replaced Value"

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

Best Regards,

Dedmon Dai

[amitchandak]

@Anonymous , I would suggest to split prodcut in power query in rows and then use it

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/