Forum Discussion

cgkas's avatar
cgkas
Helper V
3 years ago
Solved

Tabulate text file formatted in blocks

Hi to all,   Maybe someone could help me withi this. Thanks in advance.   I have a file formatted like below          some text some text rule "Kod hu167" when g: CommandInput(input[...
  • wdx223_Daniel's avatar
    3 years ago

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZDBCoJAEIZfZZiTQQRlWQQettpKKpOyyFwPgUsFuYquRG+fGUVdMp3DzB7+Yef7XBfj9MKB4Sz04ZQ2tS5D9OouXk9c5A/I6tiHYRgEB+EbIkqlcn50l+HSoitiG0uToQe6DvmC/NyMeRKFIuGNhEsqZHxTGO6cPcN69mcw7GgMaz/DhMBk6jzzZliUXq+2ryiQV5gLP595+8BttsqwbqwRsWk1UIu21Ljo9vFY/RdzQTZvzEU5g7At43BQ5FBtl3E4onNa1aG0nV736xzvDg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
        Custom1 = let
                     fx=(t,s)=>Text.StartsWith(Text.Trim(t),s),
                     TBD=(x,y,z)=>Text.BetweenDelimiters(x,y,z),
                     Txt2Table=(t)=>Table.Combine(
                                                  Table.Group(
                                                              t,
                                                              "Column1",
                                                              {"n",each let
                                                                           r=List.Accumulate(
                                                                                             [Column1],
                                                                                             [],
                                                                                             (x,y)=>if fx(y,"rule ") then x&[RULE=TBD(y,"""","""")] else
                                                                                                    if fx(y,"g: ") then x&[INPUT TYPE=TBD(y,"""",""""),INPUT ID=Text.Trim(TBD(y,"==",")"))] else
                                                                                                    if fx(y,"response.") then x&Record.AddField([],TBD(y,"(""",""","),TBD(y,", """,""")")) else x
                                                                                            )
                                                                        in
                                                                          if r=[] then #table(0,{})
                                                                          else Table.FromRecords({r})
                                                              },
                                                              0,
                                                              (x,y)=>Byte.From(fx(y,"rule "))
                                                             )[n]
                                                 )
                   in
                     Txt2Table(Source)
    in
        Custom1