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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shivambhat
Helper I
Helper I

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 @shivambhat

@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 @shivambhat,

 

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
shivambhat
Helper I
Helper I

This  works!  thank  you  so much for your  help 🙂

shivambhat
Helper I
Helper I

@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.

@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 @shivambhat,

 

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 @shivambhat

@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

 

shivambhat
Helper I
Helper I

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.

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors