Expression.Error: We cannot convert a value of type Record to type Text.

[rob1123]

Hey,

I'm trying to work with a JSON file and coming into this error when extracting values. At the moment I have:

Col 1   Col2    Col3    Col4

Value   Value  Value   List

Within the list there are three values:

1) Unix timestamp

2) Non-decimal number

3) Either

    A Record OR

    A Null value

When I extract the value and concatenate them with a colon it works perfectly, however if the list contains a record I get an error with the title of this thread.

Is it possible to either remove the record in the list (its not particularly useful for me) or resolve it so each cell concatenates as:

{unix timestamp}:{non-decimal number}:{record contents}

Any help would be appreciated!

[Anonymous]

Hello, the below works fine but how can I write the query if I have a list that ONLY contains records. The records may have multiple values within them. Also, some rows may not have any records at all so I would like it to ignore blanks.

Any help would be appreciated.

Thanks

Brett

[Anonymous]

@MarcelBeugmaybe you could help me with the same problem.

I got the error: A value of type "record" cannot be converted to typ "text".

Details:

Value=

self=https://XXXXXXX./rest/api/2/....

value=XS-HWR

id=11111

Type=[Type]

This is my code I use:

let
    Quelle = Json.Document(Web.Contents("https://XXXXX.XXXXXX.com/rest/api/2/search?jql=project%20in%20(SRVINQ)&fields=aggregatetimeoriginalestimate,timeestimate,timespent,customfield_13100,customfield_13101&startAt=1&maxResults=1000")),
    #"In Tabelle konvertiert" = Record.ToTable(Quelle),
    Value = #"In Tabelle konvertiert"{4}[Value],
    #"In Tabelle konvertiert1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert1", "Column1", {"expand", "id", "self", "key", "fields"}, {"Column1.expand", "Column1.id", "Column1.self", "Column1.key", "Column1.fields"}),
    #"Erweiterte Column1.fields" = Table.ExpandRecordColumn(#"Erweiterte Column1", "Column1.fields", {"customfield_13100", "customfield_13101", "aggregatetimeoriginalestimate", "timeestimate", "timespent"}, {"Column1.fields.customfield_13100", "Column1.fields.customfield_13101", "Column1.fields.aggregatetimeoriginalestimate", "Column1.fields.timeestimate", "Column1.fields.timespent"}),
    #"Entfernte Duplikate" = Table.Distinct(#"Erweiterte Column1.fields", {"Column1.fields.customfield_13100"}),
    #"Erweiterte Column1.fields.customfield_13101" = Table.ExpandRecordColumn(#"Entfernte Duplikate", "Column1.fields.customfield_13101", {"self", "value", "id"}, {"Column1.fields.customfield_13101.self", "Column1.fields.customfield_13101.value", "Column1.fields.customfield_13101.id"}),
    #"Entfernte oberste Zeilen" = Table.Skip(#"Erweiterte Column1.fields.customfield_13101",1),
    #"Extrahierte Werte" = Table.TransformColumns(#"Entfernte oberste Zeilen", {"Column1.fields.customfield_13100", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Column1 fields customfield_13100" = #"Extrahierte Werte"{12}[Column1.fields.customfield_13100]
in
    #"Column1 fields customfield_13100"

It would be great, if you could help me. I have no clue how to handle it.

Thanks alot!

freiburgc

[MarcelBeug]

Solution with removal of record:

RecordRemoved = Table.TransformColumns(Source,{{"Col4", each List.FirstN(_,2)}}),

Solution with record to list:

RecordToList = Table.TransformColumns(Source,{{"Col4", each List.Combine({List.FirstN(_,2), if _{2} = null then {} else Record.ToList(_{2})})}}),

[rob1123]

Thanks!

Am I right in thinking I paste this into the advanced editor? If so I'm getting a new error saying:

Expression.SyntaxError: Token Comma expected.

[rob1123]

Ah there was a missing comma. However now I'm getting the following:

Value can not be null

parameter name: name

[MarcelBeug]

Yes. it is in the advanced editor.

Can you share your query code?

[MarcelBeug]

As I'm going off line I show you an example of how the code should look like.

This is code with both examples included. You can copy and paste into you advanced editor and see the results step by step.

let
    List1 = {78876491678679, 12, [SomeNumber = 13, SomeText = "MarcelBeug"]},
    List2 = {77378856782687, 23, null},
    Source = #table({"Col1","Col2","Col3","Col4"},
                    {{"A","B","C",List1},
                     {"X","Y","Z",List2}}),
    
    RecordRemoved = Table.TransformColumns(Source,{{"Col4", each List.FirstN(_,2)}}),
    #"Extracted Values" = Table.TransformColumns(RecordRemoved, {"Col4", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
    

    RecordToList = Table.TransformColumns(Source,{{"Col4", each List.Combine({List.FirstN(_,2), if _{2} = null then {} else Record.ToList(_{2})})}}),
    #"Extracted Values1" = Table.TransformColumns(RecordToList, {"Col4", each Text.Combine(List.Transform(_, Text.From), ":"), type text})

in
    #"Extracted Values1"

[jelibier]

Good day sir, ive followed your steps and it didnt work for me.
i have the same issue my column contains lists and all those lists have record as type when you drill through them and i need them all to be the value for me to extract the value and have the values next to each other.

[Anonymous]

Hi, how would you write the query if you had a list that ONLY contained records?

[MarcelBeug]

You need to replace "Source"  with the name of your previous step: #"Expanded Value.graphData".

[xander85]

Hi, I have a similar issue from an API.

It pulls multiple records, and one of the columns is a list, that then contains records.

My issue is that expanding the list generates a duplicate line if the list contains multiple records.

What I want, is to transpose the list into columns. But I am having some difficulty.

This is the code as is when I follow the GUI prompts to expand the list, then expand the records.

#"Expanded tags.data" = Table.ExpandListColumn(AllTransactions, "tags.data"),
#"Expanded tags.data1" = Table.ExpandRecordColumn(#"Expanded tags.data","tags.data", {"type","id"}, {"tags.data.type","tags.data.id"})

Where tags.data.id is what I want to keep.

Some lines have zero tags, some have 1 or 2, up to 6.

So, instead of having 6 rows with different tags. I am after one row, with 6 columns for each tag.

Then a separate column at the end to contatonate them.

The issue is it is a banking api that I am trying to run totals on, duplicate rows are a no go.

[rob1123]

Remove the URL because it contains API key, using the following:

let

Source = Json.Document(Web.Contents(),
#"1" = Source[887947640],
#"Converted to Table" = Record.ToTable(#"1"),
#"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"6014", "0"}, {"Value.6014", "Value.0"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Value",{{"Value.6014", "Top Games"}, {"Value.0", "Top Overall"}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Name"}, "Attribute", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Unpivoted Columns", "Value", {"topfreeapplications", "topgrossingapplications", "topgrossingipadapplications"}, {"Value.topfreeapplications", "Value.topgrossingapplications", "Value.topgrossingipadapplications"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Value1",{{"Value.topfreeapplications", "Free"}, {"Value.topgrossingapplications", "Grossing"}, {"Value.topgrossingipadapplications", "iPad Free"}}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"Name", "Attribute"}, "Attribute.1", "Value"),
#"Expanded Value2" = Table.ExpandRecordColumn(#"Unpivoted Columns1", "Value", {"humanized_app_name", "graphData"}, {"Value.humanized_app_name", "Value.graphData"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded Value2",{{"Value.humanized_app_name", "App"}}),
#"Expanded Value.graphData" = Table.ExpandListColumn(#"Renamed Columns2", "Value.graphData"),
#"RecordRemoved" = Table.TransformColumns(Source,{{"Value.graphData", each List.FirstN(_,2)}})
in
#"RecordRemoved"

gives

Expression.Error: We cannot convert a value of type Record to type Table.
Details:
Value=Record
Type=Type