Forum Discussion

pbix1's avatar
pbix1
Resolver II
3 months ago
Solved

Parsing list values in a nested table

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 ...
Need Help
  • pbix1's avatar
    pbix1
    3 months ago

    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's avatar
ralf_anton
Resolver I
3 months ago

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's avatar
pbix1
Resolver II
3 months ago

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