How to create more than 1 Column using field with different variables

[Anonymous]

Hi,

I uploaded a query and need to use a field that has different variables and create more than 1 column with those variables. Basically I need to segment the variables into different columns and have each column use the count. For example - if the field has colors red, white and blue for different customers, I need to create columns titled red, white and blue for each named customer on the left of my chart. The columns for each color will count the # of times each customer selected that color as a preference. I hope this helps. Thank you.

[jbwtp · Accepted solution]

Hi @Anonymous,

Most simplisitically:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpSitVBZ7mDWU4oLCc0ljNchzNcnTNENhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Colour = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Colour", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Colour]), "Colour", "Colour", List.Count)
in
    #"Pivoted Column"

If you have more criteria, you can formalise it by gtrouping to the criteria first:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpSitVBZ7mDWU4oLCc0ljNchzNcnTNENhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Colour = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Colour", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer", "Colour"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Colour]), "Colour", "Count", List.Sum)
in
    #"Pivoted Column"

Cheers,

John