Combining multiple columns for Table plot

[Anonymous]

Hi,

I have an excel sheet that looks something like this:

MerchantID	Sales_Jan	Rating_Jan	Sales_Feb	Rating_Feb
1	300	4.5	400	4.7
2	444	4.7	450	4.8

I want to create Table plots for each MerchantID.

On selecting a merchant ID "1" from filters, the table should look something like this:

Month	Sales	Rating
Jan	300	4.5
Feb	400	4.7

I am unable to figure out how to achieve the same. Someone, please help me out.

Thanks

[Anonymous]

Hi @selimovd,

Thank you so much for the quick response. The power query worked perfectly for my use case. Marking your response as the solution. 🙂

[selimovd · Accepted solution]

Hey @Anonymous ,

the easiest is, if you unpivot the columns and then split the rating and sales. Try the following Power Query as an example how to do that:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI2MACSJnqmIBLKNleK1YlWMgKxTUygIkDSFCJroRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MerchantID = _t, Sales_Jan = _t, Rating_Jan = _t, Sales_Feb = _t, Rating_Feb = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MerchantID", Int64.Type}, {"Sales_Jan", Int64.Type}, {"Rating_Jan", type number}, {"Sales_Feb", Int64.Type}, {"Rating_Feb", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"MerchantID"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Type"}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Renamed Columns", "Month", each Text.AfterDelimiter([Type], "_"), type text),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Inserted Text After Delimiter", {{"Type", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Extracted Text Before Delimiter",{"MerchantID", "Type", "Month", "Value"})
in
    #"Reordered Columns"

Afterwards you can just add a slicer and a matrix:

I attached my demo file.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic