cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Expression.Error: We cannot convert a value of type Record to type Text - source is List of records

Hey

This is a post derived from another post raised by rob1123 a while ago

(https://community.powerbi.com/t5/Desktop/Expression-Error-We-cannot-convert-a-value-of-type-Record-t...

 

In my case, I have a Col5 that is a list of records and I would like to extracts the values from the LAB field and concatenate the values with a comma as separator to get at the end a table as below

Col1Col2Col3Col5
ABCB3, T1, TK
XYZ 

 

Below is code from rob1123 that was answered by MarcelBeug in which I have inserted List3 and List4

 

 

 

let
    List3 = {[ID=21, LAB="B3"],[ID=22,LAB="T1"],[ID=23,LAB="TK"]},
    List4 = {[ID=20, LAB=""]},
    List1 = {78876491678679, 12, [SomeNumber = 13, SomeText = "MarcelBeug"]},
    List2 = {77378856782687, 23, null},
    Source = #table({"Col1","Col2","Col3","Col4","Col5"},
                    {{"A","B","C",List1,List3},
                     {"X","Y","Z",List2,List4}}),
    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"

 

 

 

 

I tried for a while but I have no idea how to proceed. I suppose I need to

- chain several TransformColumns with ListOfRecords -> TableOfLists and to combine the rows of LAB column ?

- OR to mimic https://www.youtube.com/watch?v=nJ7LzwiSwnw doing some Expands then GroupBy using "," as separator

- OR?

 

I think I need some help here...

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Expression.Error: We cannot convert a value of type Record to type Text - source is List of reco

Use this function on each row passing it the column with a list of records (the ones with LAB as one of their fields):

(listOfRecords as list) as nullable text =>
let
    RecordsWithoutNullsAndEmptyStrings =
        List.Select(
            listOfRecords,
            each not List.Contains({null, ""}, [LAB])
        ),
    ConcatenatedValues = 
        List.Accumulate(
            RecordsWithoutNullsAndEmptyStrings,
            null,
            (prevVal as nullable text, record_ as record) as nullable text =>
                if prevVal is null then
                    record_[LAB]
                else
                    prevVal & ", " & record_[LAB]
        )
in
    ConcatenatedValues

 

Best

D

View solution in original post

Highlighted
Helper I
Helper I

Re: Expression.Error: We can't convert a registry value to type Text - the source is Reco List

Perhaps it meant creating a Manual function and invoking the function from the Power Query editor. I'm going to try this tomorrow morning!! Thank you for your support

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Expression.Error: We cannot convert a value of type Record to type Text - source is List of reco

Add a new column to the table and use this function in it passing it the column with the list of records.

Best
D

View solution in original post

5 REPLIES 5
Highlighted
Anonymous
Not applicable

Re: Expression.Error: We cannot convert a value of type Record to type Text - source is List of reco

Use this function on each row passing it the column with a list of records (the ones with LAB as one of their fields):

(listOfRecords as list) as nullable text =>
let
    RecordsWithoutNullsAndEmptyStrings =
        List.Select(
            listOfRecords,
            each not List.Contains({null, ""}, [LAB])
        ),
    ConcatenatedValues = 
        List.Accumulate(
            RecordsWithoutNullsAndEmptyStrings,
            null,
            (prevVal as nullable text, record_ as record) as nullable text =>
                if prevVal is null then
                    record_[LAB]
                else
                    prevVal & ", " & record_[LAB]
        )
in
    ConcatenatedValues

 

Best

D

View solution in original post

Highlighted
Helper I
Helper I

Re: Expression.Error: We cannot convert a value of type Record to type Text - source is List of reco

Thanks a lot for your time and response darlove but I think I still need your help to understand where to insert the function in my example and his to call it.

Highlighted
Helper I
Helper I

Re: Expression.Error: We can't convert a registry value to type Text - the source is Reco List

Perhaps it meant creating a Manual function and invoking the function from the Power Query editor. I'm going to try this tomorrow morning!! Thank you for your support

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Expression.Error: We cannot convert a value of type Record to type Text - source is List of reco

Add a new column to the table and use this function in it passing it the column with the list of records.

Best
D

View solution in original post

Highlighted
Helper I
Helper I

Re: Expression.Error: We cannot convert a value of type Record to type Text - source is List of reco

Hey

darlove as provided a good solution, thanks to him.

 

I found another solution that I wanted to share here that is based on basic operations

This is based on Group By & Add Column

 

1/ export a 2nd time the data (my example was an abstract of my real table)

    Let's assume in my example Col1 contains an unique key

    I export here only Col1 and Col5 where Col5 contains the list of records

2/ in this export:

    a) Expand the list and the records with one row per record

    b) Group By, Col1 then new column = AllData, All row

    c) Add column, Custom Column Col6 defined as Col6 = [AllData][LAB]

    d) Expand the new column, use "," as separator

3/ As this 2nd export uses same key than the 1st one, you can simply use RELATED to get the Col6

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors