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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Group BY

Expression.Error: We cannot convert the value true to type Text

shivambhat_1-1660561412109.png

 

 

2 ACCEPTED SOLUTIONS
jbwtp
Memorable Member
Memorable Member

Hi @Anonymous, 

@ronrsnfld asked a very valid question :). Do you want to do OR or AND or something else? Also you are referring to a DAX formula, but the Errors and screenshots seems to be from Power Query.

 

This PQ code does OR on [Open], to make it ANDjust change List.Max to List.Min. Or do you need something else to happen on grouping (or do you need this in DAX)?

Table.Group(Source, {"Account"}, {{"Count", each Logical.From(List.Max(List.Transform([Open], each Number.From(Logical.From(_))))), type logical}})

 

Cheers,

John

 

View solution in original post

Hi @Anonymous,

 

This is opening/closing brackets issue around this place: type logical}}), {{"Closed". At first sight, the formula should look like this:

 

Table.Group(#"Removed Columns", {"Account Name"}, {{"Open",each Logical.From(List.Max(List.Transform([Open], each Number.From(Logical.From(_))))), type logical}, {"Closed", each Logical.From(List.Max(List.Transform([Closed], each Number.From(Logical.From(_))))), type logical}})

 

 

I did not test it, but should work by the lok of it.

 

Kind regards,

John

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

This  works!  thank  you  so much for your  help 🙂

Anonymous
Not applicable

@jbwtp Thank  you  so much.  The  PQ code  which you   have  pasted works. 🙂

Thats what  I was looking for  🙂   @ronrsnfld Appereciate  you from taking  this up.

I  have started  to learn about Power Query  , Power BI & DAX.  This  is  all very  new to  me, also  would appereciate  you if  you guys  could   maybe  tell me  about   good resources  for Power BI, DAX & Power Query.Currently  I am just learning in BITS  & pieces from Youtube.

Anonymous
Not applicable

@jbwtp   Sorry I just came across   syntax issue for this-

 

Table.Group(#"Removed Columns", {"Account Name"}, {{"Open",each Logical.From(List.Max(List.Transform([Open], each Number.From(Logical.From(_))))), type logical}}), {{"Closed", each Logical.From(List.Max(List.Transform([Closed], each Number.From(Logical.From(_))))), type logical}})

 

shivambhat_0-1660629684706.png

Please have a look and help me rectifying this.

Hi @Anonymous,

 

This is opening/closing brackets issue around this place: type logical}}), {{"Closed". At first sight, the formula should look like this:

 

Table.Group(#"Removed Columns", {"Account Name"}, {{"Open",each Logical.From(List.Max(List.Transform([Open], each Number.From(Logical.From(_))))), type logical}, {"Closed", each Logical.From(List.Max(List.Transform([Closed], each Number.From(Logical.From(_))))), type logical}})

 

 

I did not test it, but should work by the lok of it.

 

Kind regards,

John

jbwtp
Memorable Member
Memorable Member

Hi @Anonymous, 

@ronrsnfld asked a very valid question :). Do you want to do OR or AND or something else? Also you are referring to a DAX formula, but the Errors and screenshots seems to be from Power Query.

 

This PQ code does OR on [Open], to make it ANDjust change List.Max to List.Min. Or do you need something else to happen on grouping (or do you need this in DAX)?

Table.Group(Source, {"Account"}, {{"Count", each Logical.From(List.Max(List.Transform([Open], each Number.From(Logical.From(_))))), type logical}})

 

Cheers,

John

 

Anonymous
Not applicable

Thanks  for your response.

I am totally new to Power BI  Space & I am  trying to  retrieve  data  for  accounts  which are  open & closed.

And Since  I have logical operators added in the  columns and hence  I am unable to   use Group  BY  operation & that is the  reason I used this  DAX  formula text.combine to get the output. But I believe  since it is not text  but a boolean, hence the error. Can you  please suggest me what  DAx formula  can  I use here? 

A small sample of your data and desired outcome from that data sample would be helpful.

Anonymous
Not applicable

shivambhat_0-1660567733558.png

The  above image is the  sample data  which  I need  for  accounts  column using group BY  feature.

 

The  Outcome  should  be  :

shivambhat_1-1660567993942.png

This  image should  be result  without   null values or error.  Value  should be as "True" which is a bolean.

Your sample data does not show any Accounts and only has 8 rows.  How do you know which lines go with which accounts?  

 

Perhaps you should look at the List.AllTrue function?  But a more realistic data sample might be helpful.

Anonymous
Not applicable

I cannot provide  you  the  dataset since its confidential.  But  for  your  understanding  I need to  know tw  DAX formula which will help me  capture  data  for  accounts  name  that  are open or closed.  Whereever  they  are open, it will show  as TRUE  &  wherever  it is  closed  it  will show as TRUE. And  Blank for  False instead of NULL.

Since you apparently feel that even with "made-up" data, any sample you provide would breach confidentiality, I have nothing to add beyond what is in my previous posting.  If that is not working, consider hiring a consultant and having him sign an NDA.

ronrsnfld
Super User
Super User

What are you trying to accomplish?  You code and error message makes it appear as if you are trying to combine the entries in a logical type column (containing either true or false) into a text string, and then typing the result as logical. That makes no sense.

 

A small sample of your data and desired outcome from that data sample would be helpful.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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