Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Line Chart with Multi-Output Data

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...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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