Forum Discussion
split columns with null result
hi, i am trying to split the column "transact" into 02 columns(first picture). but each time i try i got null result on the second column. is there something wrong that i am doing ? let
...
That's very different from splitting the column.
One solution: (Paste code into Advanced Editor)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQByIjAyNjJR2l/LxUIAlkxupEK0H4SUAKzDU01AciqMKS8nyQQhOYQiAfodBIH4hgCjOKUsFmmsKVgkQgimMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, transaction = _t, value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"transaction", type text}, {"value", Int64.Type}}),
#"Add Shifted Transactions" = Table.FromColumns(
{#"Changed Type"[date]}
& {#"Changed Type"[transaction]}
& {List.Skip(#"Changed Type"[transaction])}
& {#"Changed Type"[value]},
type table[date=date, transaction.1=text, transaction.2=text, value=number]),
#"Filtered Rows" = Table.SelectRows(#"Add Shifted Transactions", each ([date] <> null))
in
#"Filtered Rows"Data
Results
thank you, could you explain me what wrong with my code?
i try to learn from my mistake and your code look very sophisticated for me.
Your code is not appropriate for your problem. Your problem isn't to split the column (which your code would do, if each cell had a string which included a linefeed character). Splitting the column means taking each cell and splitting it into two columns. But that's not what you really want to do. What you want to do is much better stated in your follow-up post to which I responded: "move the second line of transact column, up to line who contain the date."