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
...
Hi yokaso ,
Thanks for Omid_Motamedise reply.
Based on the code you provided, it seems that you want to split the TRANSACT columns to get to the point where all the data has dates and values, if so, you can try the following code
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, Transact = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Transact", type text}, {"Value", Int64.Type}}),
FillDown = Table.FillDown(Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), {"Date"}),
FilteredRows = Table.SelectRows(FillDown, each ([Date] <> null)),
GroupedRows = Table.Group(FilteredRows, {"Date"}, {{"AllData", each _, type table [Date=nullable date, Transact=nullable text, Value=nullable number, Index=Int64.Type]}}),
AddCustom = Table.AddColumn(GroupedRows, "Custom", each let
TransactList = [AllData][Transact],
Transact1 = if List.Count(TransactList) > 0 then TransactList{0} else null,
Transact2 = if List.Count(TransactList) > 1 then TransactList{1} else null
in
[Transact1=Transact1, Transact2=Transact2]),
ExpandCustom = Table.ExpandRecordColumn(Table.ExpandTableColumn(AddCustom, "AllData", {"Value"}, {"Value"}), "Custom", {"Transact1", "Transact2"}),
RemoveNulls = Table.SelectRows(ExpandCustom, each [Value] <> null),
#"Renamed Columns" = Table.RenameColumns(RemoveNulls,{{"Transact1", "Transact"}, {"Transact2", "Transactb"}})
in
#"Renamed Columns"Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
thank you, but to clarify what i am looking for.
- i would like to move the second line of transact column, up to line who contain the date. like that , i will get 2 value for the same date.
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.
