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

Question

Hi,&nbsp;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 Answer

Hi&nbsp;Anonymous,&nbsp;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"&nbsp;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"&nbsp;Cheers,John&nbsp;

Forum Discussion

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

1 Reply

Related content

Creating a dynamic filtered table variable

Unable to Create Variable library

Different schema into different destination

How to avoid people creating multiple times the same table in different workspaces

Create a report per Worker ID with different datasets feeding different tablix