Parsing list values in a nested table

[pbix1]

Hi

The title of this post is essentially what I am trying to achieve, but the issue is fairly involved so I'll provide a bit of background.

I have a dataflow that comes to a step which has a table containing number of a columns. One of these is a table column that holds a series of transform files. The step that follows this filters the tables in that column at row level by a value in one of the other columns which are non table columns, (so it's filtering the pre-expanded tables in the table column by a value in one of the other columns of the host table). These filter values vary across each row of the host table. The above works, but there are different sets of source files to process that are in different formats that I need to apply this logic to.

The issue is -

Some of these source/transform files contain the field with the date that the filter needs to be applied to in yyyyMMdd format. This format directly corresponds to the filter that I am applying from the parent level table, with which the above works OK.

However, some of the sources contain the field with the date that needs filtering in the format yyyy-MM-ddThh:mm:ssZ. I've figured out how I can add a column into these tables so that if these contain whatever date needs filtering it can be referred to with the existing logic outside of these tables. The idea there is to add a column that derives yyyyMMdd from yyyy-MM-ddThh:mm:ssZ. I've got most of the way there using List.ReplaceValues to get yyyyMMddThh:mm:ssZ, but don't know how to get rid of the Thh:mm:ssZ part.

What I really want to do is make the new column flexible, so I can adjust it to either:

Text.Middle([SYSTIMESTAMP],0,4)&Text.Middle([SYSTIMESTAMP],5,2)&Text.Middle([SYSTIMESTAMP],8,2)

or

Text.Middle([PROCESS_DATE],0,8)

I can then refer to this single column inside the transform tables, and filter against that by just adjusting the above according to the file format.

The bottom of this post contains the M code which demonstrates what I want to do with this. In the 'Transform File', I would like the column 'Reference_Date' to be derivable from either 'Process_Date' or 'SysTimestamp'. The columns 'REFERENCE_DATE_proc' and 'REFERENCE_DATE_sys' show what I would like to incorporate into the generated 'Reference_Date' column inside the 'Transform File'.

So to replace the part below with the Text.Middle() code above:

List.ReplaceValue([SysTimestamp],"-","",Replacer.ReplaceText)

If anyone can help with this I would really appreciate it. I'm thinking it would require yet a further iteration, but I can't work out how to build this into it. Many thanks.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg5yNjBU0lEyMjAyMzAxMoEydQ1MdI1MQgyNrAwMgChKKVYHQ60xCWqNcKqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FILE_CODE = _t, PROCESS_DATE = _t, SYSTIMESTAMP = _t]),
    Change_Type_01 = Table.TransformColumnTypes(Source,{{"FILE_CODE", type text}, {"PROCESS_DATE", type text}, {"SYSTIMESTAMP", type text}}),
    Add_Transform_File = Table.AddColumn(Change_Type_01, "Transform File", 
each Table.TransformColumnTypes(
Table.FromRecords({
    [Process_Date = "20260424", SysTimestamp = "2026-04-24T12:00:00Z"],
    [Process_Date = "20260423", SysTimestamp = "2026-04-24T12:00:00Z"],
    [Process_Date = "20260422", SysTimestamp = "2026-04-24T12:00:00Z"]
}),{"Process_Date", type text, "SysTimestamp", type text}
)
),
    Add_SysDate = Table.TransformColumns(Add_Transform_File,
{"Transform File",each Table.FromColumns(Table.ToColumns(_)&{List.ReplaceValue([SysTimestamp],"-","",Replacer.ReplaceText)},Table.ColumnNames(_)&{"Reference_Date"})}
),
    Add_REFERENCE_DATE_proc = Table.AddColumn(Add_SysDate, "REFERENCE_DATE_proc", each Text.Middle([PROCESS_DATE],0,8)),
    Add_REFERENCE_DATE_sys = Table.AddColumn(Add_REFERENCE_DATE_proc, "REFERENCE_DATE_sys", each Text.Middle([SYSTIMESTAMP],0,4)&Text.Middle([SYSTIMESTAMP],5,2)&Text.Middle([SYSTIMESTAMP],8,2)),
    Change_Type_02 = Table.TransformColumnTypes(Add_REFERENCE_DATE_sys,{{"REFERENCE_DATE_proc", type text}, {"REFERENCE_DATE_sys", type text}})
in
    Change_Type_02

[ronrsnfld]

I'm not sure exactly what you want to do. But one of the following, inserted after your `Change_Type_01` should provide something for you to work with.

Note that Text.BeforeDelimiter will return the entire string if Delimiter is not present.
Also note that Date.From will return the proper date from either  yyyyMMdd format or  yyyy-MM-dd format.

To add a column with the actual date:

#"Added Custom" = Table.AddColumn(Change_Type_01, "Date", each Date.From(Text.BeforeDelimiter([SYSTIMESTAMP],"T")), type date)

To transform the SYSTIMESTAMP column into an actual date:

#"Transform to Date" = Table.TransformColumns(Change_Type_01,{"SYSTIMESTAMP", each Date.From(Text.BeforeDelimiter(_,"T"))})

[pbix1]

Thanks both for the replies. That's not what I'm after I'm afraid though.

What I'm trying to do is do this within the pre-expanded 'Transform File'. The code I posted is just sample code to replicate the issue. In the actual scenario I need to add a Reference_Date column to unexpanded tables held in a Transform File column.

The columns Add_Reference_Date_proc and Add_Reference_Date_sys only show what I would like the Reference_Date column to do instead of the List.ReplaceValue part in the Add_SysDate step. The Add_SysDate step should really be called Add_Reference_Date to match the name of the column it is adding.

The trouble I'm having is that while List.ReplaceValue works for what it does within that line of code, I would like to replace that part with Text.Middle([SysTimestamp,X,X)&Text.Middle([SysTimestamp,X,X) etc. so I can pick out the characters I want into being the Reference_Date. This itself doesn't work within that set of code in the step though, at least I can't get it to. I think it may need a further set of iteration but I can't work out how to build that into it.

I hope that makes it clearer and really appreciate any help. Thanks..

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg5yNjBU0lEyMjAyMzAxMoEydQ1MdI1MQgyNrAwMgChKKVYHQ60xCWqNcKqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FILE_CODE = _t, PROCESS_DATE = _t, SYSTIMESTAMP = _t]),
    Change_Type_01 = Table.TransformColumnTypes(Source,{{"FILE_CODE", type text}, {"PROCESS_DATE", type text}, {"SYSTIMESTAMP", type text}}),
    Add_Transform_File = Table.AddColumn(Change_Type_01, "Transform File", 
each Table.TransformColumnTypes(
Table.FromRecords({
    [Process_Date = "20260424", SysTimestamp = "2026-04-24T12:00:00Z"],
    [Process_Date = "20260423", SysTimestamp = "2026-04-24T12:00:00Z"],
    [Process_Date = "20260422", SysTimestamp = "2026-04-24T12:00:00Z"]
}),{"Process_Date", type text, "SysTimestamp", type text}
)
),
    Add_SysDate = Table.TransformColumns(Add_Transform_File,
{"Transform File",each Table.FromColumns(Table.ToColumns(_)&{List.ReplaceValue([SysTimestamp],"-","",Replacer.ReplaceText)},Table.ColumnNames(_)&{"Reference_Date"})}
),
    Add_REFERENCE_DATE_proc = Table.AddColumn(Add_SysDate, "REFERENCE_DATE_proc", each Text.Middle([PROCESS_DATE],0,8)),
    Add_REFERENCE_DATE_sys = Table.AddColumn(Add_REFERENCE_DATE_proc, "REFERENCE_DATE_sys", each Text.Middle([SYSTIMESTAMP],0,4)&Text.Middle([SYSTIMESTAMP],5,2)&Text.Middle([SYSTIMESTAMP],8,2)),
    Change_Type_02 = Table.TransformColumnTypes(Add_REFERENCE_DATE_sys,{{"REFERENCE_DATE_proc", type text}, {"REFERENCE_DATE_sys", type text}})
in
    Change_Type_02

[ralf_anton]

Ok... mal sehen, ob ich es diesmal richtig verstanden habe. Du möchtest eine Referenz auf die Spalten Process_Date und SysTimestamp der Tabelle aus der Spalte TransformFile des Schrittes AddTransformFile?

Da die Tabelle der Spalte TransformFile eine Tabelle mit 3 Datensätzen enthält, ist es notwendig, dem System mitzuteilen, auf welche Zeile sich bezogen werden soll. Dazu wird eine Indexspalte benötigt. Der M-Code wäre dann folgender:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg5yNjBU0lEyMjAyMzAxMoEydQ1MdI1MQgyNrAwMgChKKVYHQ60xCWqNcKqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FILE_CODE = _t, PROCESS_DATE = _t, SYSTIMESTAMP = _t]),
    Split = Table.SplitColumn(Source, "SYSTIMESTAMP", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"SYSTIMESTAMP"}),
    Replace = Table.ReplaceValue(Split,"-","",Replacer.ReplaceText,{"SYSTIMESTAMP"}),
    Change_Type_01 = Table.TransformColumnTypes(Replace,{{"FILE_CODE", type text}, {"PROCESS_DATE", type text}, {"SYSTIMESTAMP", type text}}),

    
    Add_Transform_File = Table.AddColumn(Change_Type_01, "Transform File", 
            each Table.TransformColumnTypes(
            Table.FromRecords({
                [Process_Date = "20260424", SysTimestamp = "2026-04-24T12:00:00Z"],
                [Process_Date = "20260423", SysTimestamp = "2026-04-24T12:00:00Z"],
                [Process_Date = "20260422", SysTimestamp = "2026-04-24T12:00:00Z"]
            }),{"Process_Date", type text, "SysTimestamp", type text}
            )
            ),

    AddIndex = Table.AddIndexColumn(Add_Transform_File, "Index", 0, 1, Int64.Type),
    AddProcDate = Table.AddColumn(AddIndex, "REFERENCE_DATE_proc", each 
        [Transform File][Process_Date]{[Index]},type text),
    AddSystime = Table.AddColumn(AddProcDate, "REFERENCE_DATE_sys", each Text.Replace( Text.BeforeDelimiter( [Transform File][SysTimestamp]{[Index]},"T"),"-",""),type text)
in
    AddSystime

[pbix1 · Accepted solution]

Thanks @ralf_anton and @ronrsnfld for your suggestions.

I eventually managed to get what I needed. It may be that I didn't explain well enough what I was trying to achieve, but the revised code below creates a 'Reference_Date' column within the Transform File(s) that can be switched and manipulated using Process_Date and SysTimestamp.

Here is the code, and I hope it helps someone else trying to achieve something similar. Thanks.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg5yNjBU0lEyMjAyMzAxMoEydQ1MdI1MQgyNrAwMgChKKVYHQ60xCWqNcKqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FILE_CODE = _t, PROCESS_DATE = _t, SYSTIMESTAMP = _t]),
Change_Type_01 = Table.TransformColumnTypes(Source,{{"FILE_CODE", type text}, {"PROCESS_DATE", type text}, {"SYSTIMESTAMP", type text}}),
Add_Transform_File = Table.AddColumn(Change_Type_01, "Transform File",
each Table.TransformColumnTypes(
Table.FromRecords({
[Process_Date = "20260424", SysTimestamp = "2026-04-24T12:00:00Z"],
[Process_Date = "20260423", SysTimestamp = "2026-04-24T12:00:00Z"],
[Process_Date = "20260422", SysTimestamp = "2026-04-24T12:00:00Z"]
}),{"Process_Date", type text, "SysTimestamp", type text}
)
),
Add_Reference_Date = Table.TransformColumns(Add_Transform_File,
{"Transform File",each Table.FromColumns(Table.ToColumns(_)&{List.Transform([SysTimestamp],each Text.Middle(_,0,4)&Text.Middle(_,5,2)&Text.Middle(_,8,2))},Table.ColumnNames(_)&{"Reference_Date"})}
)
in
Add_Reference_Date

[ralf_anton]

Hi, 

---

@pbix1 wrote:

...but don't know how to get rid of the Thh:mm:ssZ part.

Zum Beispiel so: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg5yNjBU0lEyMjAyMzAxMoEydQ1MdI1MQgyNrAwMgChKKVYHQ60xCWqNcKqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FILE_CODE = _t, PROCESS_DATE = _t, SYSTIMESTAMP = _t]),

//**********************************************************************************************
    Split = Table.SplitColumn(Source, "SYSTIMESTAMP", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"SYSTIMESTAMP"}),
    Replace = Table.ReplaceValue(Split,"-","",Replacer.ReplaceText,{"SYSTIMESTAMP"}),
    Change_Type_01 = Table.TransformColumnTypes(Replace,{{"FILE_CODE", type text}, {"PROCESS_DATE", type text}, {"SYSTIMESTAMP", type text}}),