How to convert a Column that contains List Value and inside of this list value, contains a Record vl

[jasonfordx]

Dear all, good afternoon, how are you?

I am facing a problem that I am not able to solve by myself.

I have a big query, reading data from different APIs, which make every alteration consumes 1 minute, but anyway

My situation is, I am doing a research and I need to read a data inside of that Column (investment.returns.monthly.2016):

But that column contain a LIST value per each row as you can see. Since this column contains 12 records (12 months) and inside of each record, contains 3 values (price at the first day of the month, price at the last day of the month, %variace of the month).

I Would like to convert this column into 12 different columns containing just one record, the first day of the month. Could you gentlemen help me to solve this issue?

Kind Regards,

[v-chuncz-msft]

@jasonfordx,

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/JSON-gt-Parse-List-of-List-of-Records/m-p/517201#M241801

[ImkeF]

The solution from @v-chuncz-msft is correct, but in order to apply it to your specific case, you'd have to modify it like this:

Table.TransformColumns(<PreviousStepName>, {{"investment.returns.monthly.2016", each Table.Transpose(Table.FromList(List.Transform(_, each Record.Field(_, "value"))))}})

Where you have to replace <PreviousStepName> by the name of the previous step in the query editor.

[jasonfordx]

Hi Mrs. Imkef, how are you?

First of all, thanks for your time and support.

Probably I did something wrong because didnt work. These are my last steps:

    #"Expanded investment.returns" = Table.ExpandRecordColumn(#"Expanded investment.historicals", "investment.returns", {"monthly", "yearly"}, {"investment.returns.monthly", "investment.returns.yearly"}),
    #"Expanded investment.returns.yearly" = Table.ExpandRecordColumn(#"Expanded investment.returns", "investment.returns.yearly", {"2016", "2017", "2018"}, {"investment.returns.yearly.2016", "investment.returns.yearly.2017", "investment.returns.yearly.2018"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded investment.returns.yearly", {"exchanges", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Expanded investment.returns.monthly" = Table.ExpandRecordColumn(#"Extracted Values", "investment.returns.monthly", {"2016", "2017", "2018"}, {"investment.returns.monthly.2016", "investment.returns.monthly.2017", "investment.returns.monthly.2018"})
in
    #"Expanded investment.returns.monthly"

[ImkeF]

So you have to add my formula as the last step like so for example:

    #"Expanded investment.returns" = Table.ExpandRecordColumn(#"Expanded investment.historicals", "investment.returns", {"monthly", "yearly"}, {"investment.returns.monthly", "investment.returns.yearly"}),
    #"Expanded investment.returns.yearly" = Table.ExpandRecordColumn(#"Expanded investment.returns", "investment.returns.yearly", {"2016", "2017", "2018"}, {"investment.returns.yearly.2016", "investment.returns.yearly.2017", "investment.returns.yearly.2018"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded investment.returns.yearly", {"exchanges", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Expanded investment.returns.monthly" = Table.ExpandRecordColumn(#"Extracted Values", "investment.returns.monthly", {"2016", "2017", "2018"}, {"investment.returns.monthly.2016", "investment.returns.monthly.2017", "investment.returns.monthly.2018"}),
LastStep = Table.TransformColumns(#"Expanded investment.returns.monthly" , {{"investment.returns.monthly.2016", each Table.Transpose(Table.FromList(List.Transform(_, each Record.Field(_, "value"))))}})
in
LastStep

[jasonfordx]

Into that Column: investment.returns.monthly.2016 has 12 RECORD values. For each RECORD, has 3 different values.

I am saying that, because after I used your suggestion, this is the error:

I would like to expand this column: investment.returns.monthly.2016 into 12 different columns, and them, expand these new columns in three new ones.

[ImkeF · Accepted solution]

Please try replacing "value" by "s" like so:

LastStep = Table.TransformColumns(#"Expanded investment.returns.monthly" , {{"investment.returns.monthly.2016", each Table.Transpose(Table.FromList(List.Transform(_, each Record.Field(_, "s"))))}})