Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
msantillan
Helper II
Helper II

"Like" Translate to M query

Can anyone help me translate this into M query? I have this DAX query where I need to see this in power query because of columns I need to aggregate. Thanks.

 

Check Type =
if('AUM Config'[MOD_REPORTING]=BLANK()&&'AUM Config'[AFFILIATION]="AMG Managed"&&'AUM Config'[Capital commitment/Notes etc for exclusion]="Include",
if(SEARCH("Life Insurance", 'AUM Config'[CLIENT_NAME],,0)<>0||'AUM Config'[CLIENT_NAME]="AGL SA IV Intermediate Government MBS Subaccount",
"L&R",
if(SEARCH("AMERICAN HOME ASSURANCE COMPANY", 'AUM Config'[CLIENT_NAME],,0)=0,"Reconciling item","General insurance")
),BLANK())

 

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @msantillan 

 

Try this.

if [MOD_REPORTING] = "" and [AFFILIATION] = "AMG Managed" and [#"Capital commitment/Notes etc for exclusion"] = "Include" then if Text.Contains( [CLIENT_NAME], "Life Insurance" ) or [CLIENT_NAME] ="AGL SA IV Intermediate Government MBS Subaccount" then "L&R" else if not Text.Contains( [CLIENT_NAME], "AMERICAN HOME ASSURANCE COMPANY" ) then "Reconciling item" else "General insurance" else ""

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

 

View solution in original post

4 REPLIES 4
Mariusz
Community Champion
Community Champion

Hi @msantillan 

 

Can you create a data sample for testing?

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 

Mariusz
Community Champion
Community Champion

Hi @msantillan 

 

Try this.

if [MOD_REPORTING] = "" and [AFFILIATION] = "AMG Managed" and [#"Capital commitment/Notes etc for exclusion"] = "Include" then if Text.Contains( [CLIENT_NAME], "Life Insurance" ) or [CLIENT_NAME] ="AGL SA IV Intermediate Government MBS Subaccount" then "L&R" else if not Text.Contains( [CLIENT_NAME], "AMERICAN HOME ASSURANCE COMPANY" ) then "Reconciling item" else "General insurance" else ""

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

 

@Mariusz Your solution had an error, however I just replaced the blank ("") values into null and it worked perfectly. Many thanks!!

 


@Mariusz wrote:

Hi @msantillan 

 

Try this.

 

if [MOD_REPORTING] = "" and [AFFILIATION] = "AMG Managed" and [#"Capital commitment/Notes etc for exclusion"] = "Include" then if Text.Contains( [CLIENT_NAME], "Life Insurance" ) or [CLIENT_NAME] ="AGL SA IV Intermediate Government MBS Subaccount" then "L&R" else if not Text.Contains( [CLIENT_NAME], "AMERICAN HOME ASSURANCE COMPANY" ) then "Reconciling item" else "General insurance" else ""

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 


 

 

@Mariusz  Hi Mariusz, I created a sample PBIx file . I'll also try the solution you suggested. 

Here is the sample

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.