The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a table like below. This time I need to add 400x,500x,300x and 401x values in the same table and show the result as “error” ? I tried the code above but I was not successful.. 😞 could you help me please ?
Solved! Go to Solution.
Hello - this will get it done. This adds a custom column which assigns a new name based on whether or not the Request_Type ends in an "x" (case insensitive) and then conditionally groups and sums.
Add a blank query, open the advanced editor and replace the contents with this script:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScxR0lEyNDAwUIrViVYKLk1OTi0uBgpZQkVMDAwqwCrAPFMIzwjCMzYwiADyjGEqDUFyJkBeLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Request_Type = _t, Count = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Request_Type", type text}, {"Count", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.EndsWith ([Request_Type], "x", Comparer.OrdinalIgnoreCase ) then "Error" else [Request_Type], type text),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"CustomSum", each List.Sum([Count]), type nullable text}},0,(x,y)=> Number.From ( x <> y))
in
#"Grouped Rows"
Result:
Hello - this will get it done. This adds a custom column which assigns a new name based on whether or not the Request_Type ends in an "x" (case insensitive) and then conditionally groups and sums.
Add a blank query, open the advanced editor and replace the contents with this script:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScxR0lEyNDAwUIrViVYKLk1OTi0uBgpZQkVMDAwqwCrAPFMIzwjCMzYwiADyjGEqDUFyJkBeLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Request_Type = _t, Count = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Request_Type", type text}, {"Count", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.EndsWith ([Request_Type], "x", Comparer.OrdinalIgnoreCase ) then "Error" else [Request_Type], type text),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"CustomSum", each List.Sum([Count]), type nullable text}},0,(x,y)=> Number.From ( x <> y))
in
#"Grouped Rows"
Result:
Thank you. It's worked.
I'm just wondering what to do if I only want to sum the 400x and 500x.
Hello again - you would just need to modify the second line highlighted below with the logic that should be used to determine which rows should be summed. The first highlighted line below is just a comment to help you,
Then you can decide what to do with the additional rows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScxR0lEyNDAwUIrViVYKLk1OTi0uBgpZQkVMDAwqwCrAPFMIzwjCMzYwiADyjGEqDUFyJkBeLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Request_Type = _t, Count = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Request_Type", type text}, {"Count", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each
// Add a new column with the logic which identifies if the row should be excluded/included
if [Request_Type] = "400x" or [Request_Type] = "500x" then "Error"
else [Request_Type], type text
),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"CustomSum", each List.Sum([Count]), type nullable text}},0,(x,y)=> Number.From ( x <> y))
in
#"Grouped Rows"
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
38 | |
21 | |
20 | |
16 | |
13 |