Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Cannot convert value to type table

Hi all,   I'm pulling xml data from a web URL using PBI desktop.   When I have expanded all the columns (due to their being nested tables), I get the following error message when trying to apply ...
  • ImkeF's avatar
    ImkeF
    9 years ago

    Yes, this happens if you have mixed data types in your column to expand.

     

    This query will return the same error:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0NzJR0lY0M9QyMYJz8vOVUpVgefktz8vJKMnEoCqgpLE4tKUotA6mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [period_start = _t, period_end = _t, schedule = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"period_start", type date}, {"period_end", type date}, {"schedule", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfDates", each List.Transform({Number.From([period_start])..Number.From([period_end])}, each Date.From(_))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ListOfNewRows", each if [schedule]="once" then [period_start] else if [schedule]="monthly" then List.Distinct(List.Transform([ListOfDates], each Date.StartOfMonth(_))) else List.Distinct(List.Transform([ListOfDates], each Date.StartOfQuarter(_)))),
    #"Expanded ListOfNewRows" = Table.ExpandListColumn(#"Added Custom1", "ListOfNewRows")
in
    #"Expanded ListOfNewRows"

     

    You have to unify the result of step "Added Custom1" by transforming the record [period_start] into a list like this: {[period_start]}

     

    Code now working:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0NzJR0lY0M9QyMYJz8vOVUpVgefktz8vJKMnEoCqgpLE4tKUotA6mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [period_start = _t, period_end = _t, schedule = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"period_start", type date}, {"period_end", type date}, {"schedule", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfDates", each List.Transform({Number.From([period_start])..Number.From([period_end])}, each Date.From(_))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ListOfNewRows", each if [schedule]="once" then {[period_start]} else if [schedule]="monthly" then List.Distinct(List.Transform([ListOfDates], each Date.StartOfMonth(_))) else List.Distinct(List.Transform([ListOfDates], each Date.StartOfQuarter(_)))),
    #"Expanded ListOfNewRows" = Table.ExpandListColumn(#"Added Custom1", "ListOfNewRows")
in
    #"Expanded ListOfNewRows"
v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi Anonymous,

 

I noticed that ImkeF has provided a detailed solution in this similar thread. Following is the summary for the solution. Hope it can help others who may also have this similar issue easily find the answer. :smileyhappy:

Solution form ImkeF:

 

This seems to be a bit tricky. You add a column that checks if the content of the current row of the column to expand is of type table. In that case you take the value as it is and if not, you transform the text-value to table. That way all fields will be in table format and can be expanded:

 

let

    Source = {1, #table({"a", "A"}, {{"b", "B"}})},
    #"In Tabelle konvertiert" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Step2 = Table.AddColumn(#"In Tabelle konvertiert", "Custom", each if Value.Is([Column1], type table) then [Column1] else #table({"ConvertedText"}, {{[Column1]}})),
    #"Erweiterte Custom" = Table.ExpandTableColumn(Step2, "Custom", Table.ColumnNames(Table.Combine(Step2[Custom])), Table.ColumnNames(Table.Combine(Step2[Custom])))
in
    #"Erweiterte Custom"

 

At what point in the advnaced editor do we need to insert the code?

 


 

 

Regards