How to expand a column with values Record and Text values

Power Query
1

Hi!
I hope that this is ok, but I am coming for advice from the more experienced :slightly_smiling_face:.
I am getting data via API that are stored in List (I have to expand it several times, but that’s ok), but the last column is a combination of final value and Record (see image). So far, I was not able to come up with a solution on how to expand it in the same column while keeping the values as well. Record is again a List that contains the final values. Is there anyone who has experience with this and could help me? Thanks a lot,
blondIT
20220413 data_test.pbix (37.1 KB)

image
image441Ă—773 25.4 KB

2

Hi @blondIT,

Welcome to the Forum!
Couldn’t use the supplied file, prompted for credentials… but I’ve created this example based on your description. Just copy this M code into a new blank query

let
    Source = Table.FromColumns( {{ "df", [value = {"1".."3"}], "df", [value = {"1".."3"}] }}, {"Sample"}),
    L1 = Table.AddColumn(Source, "Example1", each if [Sample] is text then [Sample] else [Sample][value] ),
    L2 = Table.AddColumn(L1, "Example2", each if [Sample] is text then [Sample] else Text.Combine( [Sample][value], ", " ))
in
    L2

it returns this table
image

Example1. Illustrates how you can conditionally access a record if there are also text strings present.
Example2. Combines the values from the nested list found in the record.

I hope this is helpful

2 Likes
3

Hi Melissa,
BIG THANK YOU, you are an angel, really, I was completely lost here
Have a nice day,
blondIT

1 Like