Forum Discussion
Converting an Array formula from excel to dax
Hi LEO4929 ,
According to your description, it seems that if AccRef group has "OverDue", you want to show "report" in this group, right? If so, you could try to use below M code to see whether it work or not.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJR8i9LLXIpTVWK1YGJ+OWXIARc8AnEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AccRef = _t, Status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"AccRef", type text}, {"Status", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"AccRef"}, {{"Count", each Text.Combine([Status], ","), type text}, {"all", each _, type table [AccRef=text, Status=text]}}), Custom1 = Table.ReplaceValue(#"Grouped Rows", each [Count], each if Text.Contains([Count], "OverDue") then "report" else "", Replacer.ReplaceValue, {"Count"}), #"Expanded all" = Table.ExpandTableColumn(Custom1, "all", {"Status"}, {"Status"}) in #"Expanded all"Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi LEO4929 ,
According to your description, it seems that if AccRef group has "OverDue", you want to show "report" in this group, right? If so, you could try to use below M code to see whether it work or not.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJR8i9LLXIpTVWK1YGJ+OWXIARc8AnEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AccRef = _t, Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"AccRef", type text}, {"Status", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"AccRef"}, {{"Count", each Text.Combine([Status], ","), type text}, {"all", each _, type table [AccRef=text, Status=text]}}),
Custom1 = Table.ReplaceValue(#"Grouped Rows", each [Count], each if Text.Contains([Count], "OverDue") then "report" else "", Replacer.ReplaceValue, {"Count"}),
#"Expanded all" = Table.ExpandTableColumn(Custom1, "all", {"Status"}, {"Status"})
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.
Thank you for your response, and yes this is exactly the result I am after, however, I'm struggling to put your suggested code somewhere that will work, please could you advise where I need to put this code.
Many thanks
Hi LEO4929 ,
Did you use Excel to connect other datasource? If so, you could use code in this properties(Click Data->Show Queries, then click table in workbook queries, and click advanced editor, use code in it)
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.