cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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

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!

8 REPLIES 8
Highlighted
Community Champion
Community Champion

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

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})})}}),

 

 

Specializing in Power Query Formula Language (M)
Highlighted
New Member

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

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.

Highlighted
New Member

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

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

 

Value can not be null

parameter name: name

Highlighted
Community Champion
Community Champion

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

Yes. it is in the advanced editor.

Can you share your query code?

Specializing in Power Query Formula Language (M)
Highlighted
New Member

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

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

Highlighted
Community Champion
Community Champion

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

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"
Specializing in Power Query Formula Language (M)
Highlighted
Community Champion
Community Champion

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

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

Specializing in Power Query Formula Language (M)
Highlighted
New Member

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

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.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors