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.
Hi,
How do I replicate this DAX function in power query? Appreciate any help. Thanks.
SUMX('Scan Event Raw Data',IF('Scan Event Raw Data'[TimeACCEPtoAKMRFIDINACCEP]>0,'Scan Event Raw Data'[TimeACCEPtoAKMRFIDINACCEP],0)+IF('Scan Event Raw Data'[TimeACCEPtoCHMRFIDINACCEP]>0,'Scan Event Raw Data'[TimeACCEPtoCHMRFIDINACCEP],0)+IF('Scan Event Raw Data'[TimeACCEPtoMTMRFIDINACCEP]>0,'Scan Event Raw Data'[TimeACCEPtoMTMRFIDINACCEP],0)
Solved! Go to Solution.
Hi qsmith83,
You could try below M code, convert null to 0
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTICYkOlWB0IzwSIdY3gXEMgNgbzkkASpnC1YK4xRAzGRzUJogDGA8khSynFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, c1 = _t, c2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"c1", Int64.Type}, {"c2", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each (if [c1]<0 or [c1]=null then 0 else [c1]) +(if [c2]<0 or [c2]=null then 0 else [c2])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"name"}, {{"sum", each List.Sum([Custom]), type number}, {"all", each _, type table [name=text, c1=number, c2=number, Custom=number]}}),
#"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"c1", "c2", "Custom"}, {"all.c1", "all.c2", "all.Custom"})
in
#"Expanded all"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi qsmith83,
You could try below M code to see whether it work or not
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTICYkOlWB0IzwSIdY3gXEMgNgbzkkASpnC1YK4xRAzGh5oUCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, c1 = _t, c2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"c1", Int64.Type}, {"c2", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each (if [c1]>0 then [c1] else 0) +(if [c2]>0 then [c2] else 0)),
#"Grouped Rows" = Table.Group(#"Added Custom", {"name"}, {{"sum", each List.Sum([Custom]), type number}, {"all", each _, type table [name=text, c1=number, c2=number, Custom=number]}}),
#"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"c1", "c2", "Custom"}, {"all.c1", "all.c2", "all.Custom"})
in
#"Expanded all"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@dax I get an error after creating custom column [Time Accep To RFID IN Accep]
Here is M code for custom column
Do I need to Group column to get sum???
@dax please ignore the SUM I no longer need it, I just need the power query forumula below to work. Currently its showing Error as per screenshot below. When I click on Error, it says Expression.Error: We cannot convert the value null to type Logical.
(if [TimeACCEPtoAKMRFIDINACCEP]>0 then [TimeACCEPtoAKMRFIDINACCEP] else 0) + (if [TimeACCEPtoCHMRFIDINACCEP]>0 then [TimeACCEPtoCHMRFIDINACCEP] else 0) + (if [TimeACCEPtoMTMRFIDINACCEP]>0 then [TimeACCEPtoMTMRFIDINACCEP] else 0)
@dax I tried modifying query to handle null values but column result is showing all null. I believe I'm close to resolution but formula is missing something....fyi all columns in the formula are Data Type: Whole Numbers
(if [TimeACCEPtoAKMRFIDINACCEP]=null then null else if [TimeACCEPtoAKMRFIDINACCEP]>0 then [TimeACCEPtoAKMRFIDINACCEP] else 0) + (if [TimeACCEPtoCHMRFIDINACCEP]=null then null else if [TimeACCEPtoCHMRFIDINACCEP]>0 then [TimeACCEPtoCHMRFIDINACCEP] else 0) + (if [TimeACCEPtoMTMRFIDINACCEP]=null then null else if [TimeACCEPtoMTMRFIDINACCEP]>0 then [TimeACCEPtoMTMRFIDINACCEP] else 0)
Hi qsmith83,
You could try below M code, convert null to 0
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTICYkOlWB0IzwSIdY3gXEMgNgbzkkASpnC1YK4xRAzGRzUJogDGA8khSynFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, c1 = _t, c2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"c1", Int64.Type}, {"c2", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each (if [c1]<0 or [c1]=null then 0 else [c1]) +(if [c2]<0 or [c2]=null then 0 else [c2])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"name"}, {{"sum", each List.Sum([Custom]), type number}, {"all", each _, type table [name=text, c1=number, c2=number, Custom=number]}}),
#"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"c1", "c2", "Custom"}, {"all.c1", "all.c2", "all.Custom"})
in
#"Expanded all"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |