Power M Query: How to specify a condition in aggregate of Table.Group

[mikecrobp]

I really like the fact that you get to choose your own aggregate functions in M

In particular Text.Combine so that I can aggregate by concatenating text. eg databases for Server1, count = 4, names = DB1/DB2/DB3/DB4

I have an example where I have multiple applications associated with a server, some live and some now decommisioned but I want to report:

1) How many applications in total

2) How many still Live

3) What the names of the Live ones are

I can get close with:

#"Grouped Rows" = Table.Group(#"Filtered out xxx", {"ServerName", "DecomStatus", "ServerStatusReason"}, {{"AppCount", each Table.RowCount(_), type number}, {"LiveAppCount", each List.Sum([LiveApp]), type number},  {"ApplicationNames", each Text.Combine([AppName], "/"), type text}}),

LiveAppname is blank if application is not live, but this gives me blanks for the "dead" applications as well and they don't look good in app1/app2///app5

I have tried to limit the span of the last "each" to only include live apps - but I can't fathom the syntax of each with a condition (is it possible). I have tried with/without braces. But fundamentally I don't get the syntax

#"Grouped Rows" = Table.Group(#"xxx", {"ServerName", "DecomStatus", "ServerStatusReason"}, { "ApplicationNames", each ([LiveAppName] <> "") Text.Combine([LiveAppName], "#(cr)#(lf)"), type text})

[Thejeswar]

Hi @mikecrobp,

I think the below DAX statement will help you achieve your need

#"Grouped Columns"= Table.Group(#"XXX",{"ServerName","DecmStatus","ServerStatusReason"},{{"ApplicationNames", each Text.Combine([LiveApp],"/")}})

When I tested with some dummy data, this did not show me the blank spaces

[mikecrobp]

Sorry for the delay. In fact my issue is not with using text.Combine as an aggregate function.

it was to use a further qualifier as part of the "each"

I have solved this by creating 2 queries and then joining (merging) them.

Mike

[v-cherch-msft · Accepted solution]

Hi @mikecrobp

You may try to use List.Select. Show a sample as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUSpWitWBsFLArCQgKw3OgjNKlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Count", each Text.Combine(List.Select([Column2], each _ <>"") ,"/"), type text}})
in
    #"Grouped Rows"

Regards,

Cherie

[mikecrobp]

Yes. That is what I am looking for. Thank you

[v-cherch-msft]

Hi @mikecrobp

Here is the reference for you. If it is not your case, please share some simplified data sample.

https://community.powerbi.com/t5/Desktop/Agroup-and-concatenate-column-with-a-common-value/td-p/2118...

Regards,

Cherie