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

[stchln]

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

Col1	Col2	Col3	Col5
A	B	C	B3, T1, TK
X	Y	Z

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...

[Anonymous · Accepted solution]

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

[stchln · Accepted solution]

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

[Anonymous · Accepted solution]

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

[stchln]

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.

[Anonymous · Accepted solution]

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

Best
D

[stchln]

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

[stchln · Accepted solution]

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