Desktop

[DivyaJohn]

Hi,

Need help with a scenario as mentioned below:

I have data with Customers and their Products. I want to show the count of Customers based on the Product

example: there are two customers which have Web Only (C&E) , one customer has only Book Only (D), one customer has Book&Web&Insurance (A) and one Customer has Book&Insurance (B)

Im trying to show the result as in the 'Expected Result' column.  Thanks! for all your help

Customer	Products	Expected Result
A	Book	Book,Web,Insurance
A	Web
A	Insurance
B	Book	Book,Insurance
B	Insurance
C	Web	Web Only
D	Book	Book Only
E	Web	Web Only

[nsexton12 · Accepted solution]

Does the attached screenshot work for you? Or do you need to see the blank rows with other data. I can modify the code to include that if needed. If this works, the code is below. 

Please copy this code and paste it starting at the second line after opening your Advanced Editor. It should be directly after the Source line. You will need to replace every place in the code below where I have "Table1" with your query name. You can also just change your query name to Table1 and this should work.

#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Table1")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Customer", "Products"}, {"Customer", "Products"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Item", "Kind", "Hidden", "Name"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Running Count", (r) =>
List.Count(
Table.SelectRows(#"Added Index", each [Customer] = r[Customer] and [Index] <= r[Index]) [Customer])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Value", each "Value " & Number.ToText([Running Count])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index", "Running Count"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Value]), "Value", "Products"),
#"Added Custom2" = Table.AddColumn(#"Pivoted Column", "Expected Result", each if [Value 3] = null and [Value 2] <> null then [Value 1] & ", " & [Value 2] else if [Value 2] = null and [Value 3] = null then [Value 1] & " Only" else [Value 1] & ", " & [Value 2] & ", " & [Value 3]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Value 1", "Value 2", "Value 3"})
in
#"Removed Columns2"

If this works for you, please mark as a solution!

[v-huijiey-msft · Accepted solution]

Hi @DivyaJohn ,

Please follow these steps:

1. This is the original data I created:

2. Under the "Data" tab, select "New Column".

Expected Result = 
VAR a = CALCULATETABLE(
    VALUES('Table'[Products]),
    ALLEXCEPT('Table', 'Table'[Customer])
)
VAR ProductCount = COUNTROWS(a)
VAR b = CONCATENATEX(a, 'Table'[Products], ", ")
RETURN
IF(
    ProductCount > 1, b,
    IF(ProductCount = 1, CONCATENATEX(a, 'Table'[Products], "") & " Only", "")
)

3. Drag fields to the report page for display.

If you have any further questions please feel free to contact me.

pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[nsexton12 · Accepted solution]

Does the attached screenshot work for you? Or do you need to see the blank rows with other data. I can modify the code to include that if needed. If this works, the code is below. 

Please copy this code and paste it starting at the second line after opening your Advanced Editor. It should be directly after the Source line. You will need to replace every place in the code below where I have "Table1" with your query name. You can also just change your query name to Table1 and this should work.

#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Table1")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Customer", "Products"}, {"Customer", "Products"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Item", "Kind", "Hidden", "Name"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Running Count", (r) =>
List.Count(
Table.SelectRows(#"Added Index", each [Customer] = r[Customer] and [Index] <= r[Index]) [Customer])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Value", each "Value " & Number.ToText([Running Count])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index", "Running Count"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Value]), "Value", "Products"),
#"Added Custom2" = Table.AddColumn(#"Pivoted Column", "Expected Result", each if [Value 3] = null and [Value 2] <> null then [Value 1] & ", " & [Value 2] else if [Value 2] = null and [Value 3] = null then [Value 1] & " Only" else [Value 1] & ", " & [Value 2] & ", " & [Value 3]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Value 1", "Value 2", "Value 3"})
in
#"Removed Columns2"

If this works for you, please mark as a solution!

[wini_R]

Hi @DivyaJohn,

in power query you can try this code (could be simplified definitely):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLKz89WitWBcMJTk+Bsz7zi0qLEvORUsIgTslInDGlnJM0uyEpdYRKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Products = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Customer"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Grouping", each _, type table}} ),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "GroupingIndex", each Table.AddIndexColumn([Grouping], "indeks", 0, 1)),
    #"Expanded Grouping" = Table.ExpandTableColumn(#"Added Custom", "GroupingIndex", {"Products", "indeks"}, {"Products", "indeks"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Grouping", "result", each if [Count] > 1 and [indeks] = 0 then Text.Combine([Grouping][Products], ",") else if [Count] = 1 then [Grouping][Products]{0} & " only" else ""),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Customer", "Products", "result"})
in
    #"Removed Other Columns"