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
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
AddSystime
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