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
Anonymous
Not applicable

Error: We Cannot Convert a value of Type List to Type Text

Hi, I have an int column in my table that I am tryng to obtain the max of. 

eg1.PNG

 

I am using = List.Max(#"Added Custom1"[Count]) to obtain the max value, however I receive the following error. 

Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]

 

Any suggestions would be greatly appreciated.

 

8 REPLIES 8
ziufas
New Member

Hello,

please advise how to convert generated list of dates to list of text lines (to be further used in API calls);

direct application of Date.ToText function generate "Error: We Cannot Convert a value of Type List to Type Text"

 

let
start = #date(2023,1,1), end = Date.From( DateTime.LocalNow() ),

ListOfDays = List.Dates( start, Duration.Days(Duration.From(end - start)), #duration(1,0,0,0) )
in
ListOfDays

Hi @ziufas ,

 

Try this as a new custom column/custom step:

List.Transform(
    { Number.From(#date(2023, 01, 01))..Number.From(Date.From(DateTime.LocalNow())) },
    each Date.From(_)
)

 

...or this as a standalone query:

let
    Source =
    List.Transform(
        { Number.From(#date(2023, 01, 01))..Number.From(Date.From(DateTime.LocalNow())) },
        each Date.From(_)
    )
in
    Source

 

In future, I would strongly recommend that you open a new topic on the forum with questions that you have. 

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




v-jingzhang
Community Support
Community Support

Hi @Anonymous 

 

You can add a custom column to have the maximum value in Count column.

= Table.AddColumn(#"Changed Type", "Max Value", each List.Max(#"Changed Type"[Count]))

21122106.jpg

 

Then filter Count column by comparing its value with the value in Max Value column. 

= Table.SelectRows(#"Added Custom", each [Count] = [Max Value])

21122107.jpg

 

After that, you can remove the Max Value column. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

Anonymous
Not applicable

Hi @v-jingzhang,

Thank you for your reply. 
Unfortunately, the step 

= Table.AddColumn(#"Changed Type", "Max Value", each List.Max(#"Changed Type"[Count]))

produces the following

Error: We Cannot Convert a value of Type List to Type Text

Hi @Anonymous 

 

Are you able to use the User Interface to get the maximum value of Count column? Go to Transform > Statistics > Maximum.

21122201.jpg

 

Jing

Anonymous
Not applicable

This produces the exact same error message

Anonymous
Not applicable

Hi @BA_Pete,

 

I am needing to filter a table to only contain rows that are the max within the count column. Therefore, I am trying to retrive the max value within the count column and use it as a variable (although I do not necessarily need to use it as a variable and could integrate in the filtering step). 

To provide further context of what I am trying to achieve: 
I have a table of records that have I need to expand and unpivot (refer to image below)

eg1.PNG

In this example there are only 2 revisions (A and B) but this could be any number and the field names within these records are all unique across revisions. Hence, I have implemented dynamic expansion of these records. To do this, I have stored the field names from the records as lists in a separate column. 

tonoMott_0-1639805400896.png

Then I have combined these lists in the fields column

tonoMott_1-1639805443548.png

Expanded the records table using this combined list

tonoMott_2-1639805487315.png

Unpivoted these columns 

tonoMott_3-1639805528910.png

 

And finally obtained the count from the 'Value' column 

tonoMott_4-1639805577465.png

This is where I want to filter all rows to only contain those that are equal to the max within the count column (i.e. 11 in this specific case. 

The m-code can be seen below (with the api link removed for personal reasons)

let
Source = Json.Document(Web.Contents("API_Link" & Teamspace & "/" & #"Model ID" & "/revisions.json?key=" & #"API Key")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"_id", "author", "desc", "tag", "timestamp", "name", "branch", "fileType"}, {"_id", "author", "desc", "tag", "timestamp", "name", "branch", "fileType"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"_id", "author", "desc", "timestamp", "name", "branch", "fileType"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"tag", "Revision Name"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Data", each #"Retrieve Clean Unity_ID"([Revision Name])),
#"Expanded Data" = Table.ExpandTableColumn(#"Invoked Custom Function", "Data", {"Value"}, {"Value"}),
#"Added Custom" = Table.AddColumn(#"Expanded Data", "Custom", each Record.Field([Value],"idToPath")),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Errors",{"Value"}),
#"Added Custom4" = Table.AddColumn(#"Removed Columns1", "Fields", each Record.FieldNames([Custom])),
List_Example = List.Buffer(List.Combine(#"Added Custom4"[Fields])),
#"Expanded DetailsRecord" = Table.ExpandRecordColumn(#"Added Custom4", "Custom", List_Example, List_Example),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded DetailsRecord", {"Revision Name"}, "Attribute", "Value"),
#"Added Custom1" = Table.AddColumn(#"Unpivoted Other Columns", "Count", each List.Count(Text.SplitAny([Value],"_"))),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Count", Int64.Type}}),
Max_num = List.Max(#"Changed Type"[Count]),
#"Added Custom2" = Table.AddColumn(#"Changed Type", "Max Number", each Max_num),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each if [Count] = [Max Number] then [Attribute] else null),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Custom", "Revision Name"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Other Columns",{{"Custom", "Unity_id"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each ([Unity_id] <> null))
in
#"Filtered Rows"

 

Sorry for the long explanation and thank you for your help!

BA_Pete
Super User
Super User

Hi @Anonymous ,

 

I'm unable to recreate your issue as I understand it.

 

Could you let me know exactly what you are trying to achieve with this function e.g. are you trying to turn the whole table into a single number, or want to use this max value as a variable somewhere else etc.

 

Please also share your M code for this query.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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