Forum Discussion
Parsing list values in a nested table
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
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"))})
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
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 AddSystimeThanks 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
