Need help extracting single values from specific Records

[MatheusGuzman]

Hello!

Need some help trying to extract some data, begginer learning Powerquery at the moment.
I have this table, wich one of the columns has Lists.

Inside each list i has 4 records.

I need specifically one of the values inside the record, to be added to the table as a new column

Any ideas on how exactly should i do this?

Thank in advance!

[m_dekorte · Accepted solution]

Hi @MatheusGuzman 
There are a couple of ways to achieve this, depening on your specific needs:
List.First or List.Last will get you the first or last record from the list in the status field
alternatively you can use optional item access to extract an item on its zero-based index position and optional field access, here's what that could look like. 

Pick one of these lines of code and paste them into the formula area of the Custom Column dialog:

List.First([status])[creationDate]?

List.Last([status])[creationDate]?

[status]{1}?[creationDate]?

[jgeddes · Accepted solution]

You can use the Table.TransformColumns function to work within the lists in the status column. 
a sample would look like...

= Table.TransformColumns(duplicateStatusList, {{"creationDate", each List.First(List.Select(_, each Record.HasFields(_, "creationDate")))}})

In this function, for each row in the table (table is named 'duplicateStatusList' here) in the 'creationDate' column select the first value in the list of records that have 'creationDate' as the header.

You can then expand the resulting record in the 'creationDate' column.

Here is a complete example code you can paste into the advanced editor of a blank query.

let
    Source = 
    #table(
        {"ticketID", "status"}, 
        {
            {10, {[id="31"], [creationDate=#date(2024,11,21)], [action=7]}},
            {9,{[id="32"], [creationDate=#date(2024,11,22)], [action=6]}}
        }
    ),
    duplicateStatusList = Table.AddColumn(Source, "creationDate", each [status]),
    selectFirstCreationDate = Table.TransformColumns(duplicateStatusList, {{"creationDate", each List.First(List.Select(_, each Record.HasFields(_, "creationDate")))}}),
    expandRecord = Table.ExpandRecordColumn(selectFirstCreationDate, "creationDate", {"creationDate"}, {"creationDate"})
in
    expandRecord

Hope this helps.

[Omid_Motamedise · Accepted solution]

Use the below pattern and replace each part with the desired name or number

Tablename[columnname]{row number}[record field name]

[Anonymous · Accepted solution]

Hi @MatheusGuzman ,

You could add a costom column by this formula

=[status]{0}[creationDate]

I've made a test for your reference:

Best Regards,

Bof

[Omid_Motamedise · Accepted solution]

Use the below pattern and replace each part with the desired name or number

Tablename[columnname]{row number}[record field name]

[PwerQueryKees]

From the UI, Hit the Add Custom Column, choose the new column name and use [Status]{1} as the formula

This is what it will look like in tghe formula bar:

= Table.AddColumn(#"Tipo Alterado", "StatusCreation", each [Status]{1})

[jgeddes · Accepted solution]

You can use the Table.TransformColumns function to work within the lists in the status column. 
a sample would look like...

= Table.TransformColumns(duplicateStatusList, {{"creationDate", each List.First(List.Select(_, each Record.HasFields(_, "creationDate")))}})

In this function, for each row in the table (table is named 'duplicateStatusList' here) in the 'creationDate' column select the first value in the list of records that have 'creationDate' as the header.

You can then expand the resulting record in the 'creationDate' column.

Here is a complete example code you can paste into the advanced editor of a blank query.

let
    Source = 
    #table(
        {"ticketID", "status"}, 
        {
            {10, {[id="31"], [creationDate=#date(2024,11,21)], [action=7]}},
            {9,{[id="32"], [creationDate=#date(2024,11,22)], [action=6]}}
        }
    ),
    duplicateStatusList = Table.AddColumn(Source, "creationDate", each [status]),
    selectFirstCreationDate = Table.TransformColumns(duplicateStatusList, {{"creationDate", each List.First(List.Select(_, each Record.HasFields(_, "creationDate")))}}),
    expandRecord = Table.ExpandRecordColumn(selectFirstCreationDate, "creationDate", {"creationDate"}, {"creationDate"})
in
    expandRecord

Hope this helps.

[m_dekorte · Accepted solution]

Hi @MatheusGuzman 
There are a couple of ways to achieve this, depening on your specific needs:
List.First or List.Last will get you the first or last record from the list in the status field
alternatively you can use optional item access to extract an item on its zero-based index position and optional field access, here's what that could look like. 

Pick one of these lines of code and paste them into the formula area of the Custom Column dialog:

List.First([status])[creationDate]?

List.Last([status])[creationDate]?

[status]{1}?[creationDate]?