Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "transact", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), 2)
in
#"Split Column by Delimiter"
.
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.
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
is the solution work for this case :
You need to figure out what you want and express it clearly. You also need to provide data examples that encompass the variability you might have, and what you really expect for results. In a previous post, you wrote: " 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." How do you expect ot get 2 value for the same date in this new example? What do you really want for results?
what i am looking is: to put all values from Transact column who are under same previous date on the same line .
i dont know how to updalod an excel file to give you a realistic example .
But in your new example, you now have multiple Values for the same Date! In your original post, you only had a single Value for each Date. I'm afraid without examples of your expected output from you pictured input, I will not be able to assist you any further.
You keep changing the specifications with each post and I have no idea what you expect for output.
thank you and sorry for the confusion. you solution is good enough for me .
but if you could explain me what u did the second step ?
@ronrsnfld wrote:But in your new example, you now have multiple Values for the same Date! In your original post, you only had a single Value for each Date. I'm afraid without examples of your expected output from you pictured input, I will not be able to assist you any further.
You keep changing the specifications with each post and I have no idea what you expect for output.
@ronrsnfld wrote:But in your new example, you now have multiple Values for the same Date! In your original post, you only had a single Value for each Date. I'm afraid without examples of your expected output from you pictured input, I will not be able to assist you any further.
You keep changing the specifications with each post and I have no idea what you expect for output.
@ronrsnfld wrote:But in your new example, you now have multiple Values for the same Date! In your original post, you only had a single Value for each Date. I'm afraid without examples of your expected output from you pictured input, I will not be able to assist you any further.
You keep changing the specifications with each post and I have no idea what you expect for output.
#"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]),
That could also be written:
#"Add Shifted Transactions" = Table.FromColumns(
Table.ToColumns(#"Changed Type")
& {List.Skip(#"Changed Type"[transaction])},
type table[date=date, transaction.1=text, value=number, transaction.2=text]),
It is creating List for each of the columns in the table, and then adding another column where we Skip the first entry in the transaction column (hence moving it up one).
Examine MS Help for those functions to better understand.
Doing it this way results in the transactions.2 column being the last column, so you need to re-arrange the columns to obtain the desired order.
In the initial code, I controlled the column order by entering each column individually, instead of adding all the original columns and then appending the shifted column.
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."
I guess this formula can help you, Otherwise please provide your smple data as table here to provide the solution based on your data
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "transact", Splitter.SplitTextByDelimiter("#(lf)#(lf)", QuoteStyle.Csv), 2)
in
#"Split Column by Delimiter"
i tried to upload excel file, but couldnt find an option for it ....sorry
thank you, but your solution give the same output that i got .
Please provide the sample file or sample data as table here
i tried to upload excel file, but couldnt find an option for it ....sorry
Hello there,
It looks like you are trying to split the column using a line feed string. This refers to a line break, but there is none at your column.
any suggestion to resolve my issue ?
Check out the July 2025 Power BI update to learn about new features.