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"
ImkeF's avatar
ImkeF
Community Champion
9 years ago

Hi Peter,

having problems understanding what you mean here:

If the column has integers and nulls in it, there would be no need to perform a "Table.ExpandTableColumn"-command like in the last step.

Could you please post a picture of how the content of the column looks like at step: #"Laajennettu Classifications.ItClassification" ?

Thx

PeterT's avatar
PeterT
Frequent Visitor
9 years ago

After the  #"Laajennettu Classifications.ItClassification" step, this is how the data looks like in the Classification.ItClassifications.Code column

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    Hi Pete,

    if you see the arrows, expansion should be possible. The example from this post looked like so:

     

     

    Where in the last column the arrows were missing. Because the other items in the columns have been lists, we converted the single item into a list.

    So if you want to apply this method to your case, you have to convert the value into a table instead.

     

    • PeterT's avatar
      PeterT
      Frequent Visitor
      9 years ago

      Thank you Imke for your prompt responses,

       

      As I am new to Power BI, it took me a while to figure out how to achieve converting to tables. I actually managed to do this with the help of this post by you. I'll continue the discussion there, as this step is now resolved and I have another issues I am trying to resolve, which I think is more related to that thread.

       

      Much appreciated,

      Pete