Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
It is just and extended of the below question and a curious thing that i am trying to learn.
https://community.powerbi.com/t5/Desktop/if-statement-in-group-by-Power-Query/m-p/489866#M228273
Here i got to know that we can use if conditions in power query group by function.
But what if i want to add two columns based on two if conditions by group by function.
I have added a comment also to the same that what i have tried.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZDPCoJAGMTfZc8K+0ddO0oaReJFs4N4EFtICo3NBO89Sm/ii/XtIag0NE/jN+zPGSZJUChkU+Sie0hRHkQpkYaWRd2CRCI/grhZLUDW1U1eldteBEq1BBnEJIyC09392HV8L4BvhhkBoZhwHVMdMziUsa/kaTplKM9UYTJrxPkFWlz9zom9MHIGKEsdcyBifjbk3FRvVo6/G2hHOBy9chOYrx1sTGxwvCDebIdWoHgm09vAZnTxuxulAznjRG8BmxH2b8oo8Z6SPgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Serviceärendenr", Int64.Type}, {"City", type text}, {"Tech", Int64.Type}, {"Date", type date}, {"Hours", Int64.Type}, {"Type", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"Date", "City", "Tech"}, {{"Max", each List.Max(Table.AddColumn(_,"MaxHours", each if [Type]="Travel" then [Hours] else null)[MaxHours]), type number}}, {{"Min", each List.Min(Table.AddColumn(_,"MinHours", each if [Type]="Work" then [Hours] else null)[MinHours]), type number}}) in #"Grouped Rows"
But getting error as
Expression.Error: We cannot convert a value of type List to type Number. Details: Value=List Type=Type
Can anyone please correct me what was the mistake i am doing here.
Thanks,
Mohan V
Solved! Go to Solution.
Hi @Anonymous
You may try to use below function:
= Table.Group(#"Changed Type1", {"Date", "City", "Tech"}, {{"Max", each List.Max(Table.AddColumn(_,"MaxHours", each if [Type]="Travel" then [Hours] else null)[MaxHours]), type number}, {"Min", each List.Min(Table.AddColumn(_,"MinHours", each if [Type]="Work" then [Hours] else null)[MinHours]), type number}})
Regards,
Cherie
Hi @Anonymous
You may try to use below function:
= Table.Group(#"Changed Type1", {"Date", "City", "Tech"}, {{"Max", each List.Max(Table.AddColumn(_,"MaxHours", each if [Type]="Travel" then [Hours] else null)[MaxHours]), type number}, {"Min", each List.Min(Table.AddColumn(_,"MinHours", each if [Type]="Work" then [Hours] else null)[MinHours]), type number}})
Regards,
Cherie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |