Forum Discussion
cgkas
Helper V
3 years agoTabulate 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[...
- 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
cgkas
Helper V
3 years agowdx223_Daniel Thanks so much. I'm surprised. All conversion in one step.
I don't want to bother you by asking for an explanation of the logic in your solution. Maybe you could give me some reference where I can learn how to do this type of custom functions, without having to do several steps. For example, how do you test this multi level nesting? How do you debug if everything is in one step?
wdx223_Daniel
Community Champion
3 years ago
here is some explanations.
- cgkas3 years ago
Helper V
Thank you again