cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
datahamlincoln
Frequent Visitor

Error: Cannot convert value of type List to type Text

I am using a Dataflow to fetch data from a GeoJSON source, where I use the following M code to expand the coordinate lat/lon values:

 

#"Extracted Values" = Table.TransformColumns(#"Expanded coordinates1", {"coordinates", each Text.Combine(List.Transform(_, Text.From), ","), type text}),

 

 

However, I receive the following error: Error: Expression.Error: We cannot convert a value of type List to type Text.

 

The M code above works fine in desktop, but I can't get it to work on a Dataflow. There are a few community questions/answers for similar issues, but the solution involves adjusting privacy levels on a .pbix file, then republishing. Is there a similar workaround for Dataflows on the Power BI Service?

2 REPLIES 2
v-diye-msft
Community Support
Community Support

Hi @datahamlincoln 

 

1. This error message doesn't seem to fit to the step you're describing. Sure that the error-message belongs to this last reordering step? Can you step through the single steps in the query editor after this last change has been made or don't the single steps show up anymore there?

 

2. Please check the below posts whether helps:

https://community.powerbi.com/t5/Desktop/Cannot-convert-data-type-of-a-Sharepoint-list-column/td-p/2... 

 

3. 

Use Text.Combine:

 

https://msdn.microsoft.com/en-us/library/mt253358.aspx

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Thank you for the reply, Dina Ye. Responding to your questions here:

 

1) I don't think this is a "re-ordering step" - rather, it's a table transformation step that transforms a column of type list to type text, combining the list values into a comma separated list. Maybe I'm misunderstanding your question? Here's the entire script:

let
  Source = Json.Document(Web.Contents("https://www.website/dataset1.geojson")),
  #"Converted to table" = Record.ToTable(Source),
  Navigation = #"Converted to table"{1}[Value],
  #"Converted to table 1" = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to table 1", "Column1", {"type", "properties", "geometry"}, {"type", "properties", "geometry"}),
  #"Expanded properties" = Table.ExpandRecordColumn(#"Expanded Column1", "properties", {"OBJECTID", "kccdst", "COUNCILMEM", "PHONE", "URL", "EMAIL", "Shape_Length", "Shape_Area"}, {"OBJECTID", "kccdst", "COUNCILMEM", "PHONE", "URL", "EMAIL", "Shape_Length", "Shape_Area"}),
  #"Expanded geometry" = Table.ExpandRecordColumn(#"Expanded properties", "geometry", {"type", "coordinates"}, {"type.1", "coordinates"}),
  #"Expanded coordinates" = Table.ExpandListColumn(#"Expanded geometry", "coordinates"),
  #"Expanded coordinates1" = Table.ExpandListColumn(#"Expanded coordinates", "coordinates"),
  #"Extracted Values" = Table.TransformColumns(#"Expanded coordinates1", {"coordinates", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
  #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "coordinates", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"coordinates.1", "coordinates.2"}),
  #"Changed column type" = Table.TransformColumnTypes(#"Split Column by Delimiter", {{"type", type text}, {"coordinates.1", type text}, {"coordinates.2", type text}, {"OBJECTID", Int64.Type}, {"kccdst", Int64.Type}, {"COUNCILMEM", type text}, {"PHONE", type text}, {"URL", type text}, {"EMAIL", type text}, {"Shape_Length", type number}, {"Shape_Area", type number}, {"type.1", type text}}),
  #"Renamed columns" = Table.RenameColumns(#"Changed column type", {{"coordinates.1", "longitude"}, {"coordinates.2", "latitude"}})
in
  #"Renamed columns"

2) The solution referenced here seems to deal with transforming a calculated column, which isn't present here. Are you suggesting I should create a new column for the list values instead of transforming it?

3) Text.Combine is used in the "Extracted Values" statement. Are you suggesting it should be its own step instead of being embedded in the TransformColumns expression?

 

Note #1) My original code does not trigger any errors/warnings in the query editor, and in fact the preview table appears as expected with the coordinate values separated into their own text columns. It's only when I refresh the dataflow, after saving/closing the query editor, when I get the error.

 

Note #2) This code all works in Power BI Desktop. It's only on the Dataflow service where the error occurs, so I'm relatively confident the code is correct (?). I have noticed that in Dataflow service, expanding columns via the UI does not present the user with an option to create new rows or combine values - maybe this sort of transformation just isn't wired up on the Dataflow service?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors