Error: Cannot convert value of type List to type Text

[datahamlincoln]

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?

[v-diye-msft]

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

[datahamlincoln]

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?