Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
yokaso
Regular Visitor

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
   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"

 

 

 

 

.Screenshot 2024-10-30 101054.pngScreenshot 2024-10-30 100959.png

18 REPLIES 18
Anonymous
Not applicable

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

vheqmsft_0-1730340625241.png

 

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 

ronrsnfld_0-1730855076841.png

Results

ronrsnfld_1-1730855125074.png

 

 

 

 

is the solution work for this case : Screenshot 2024-11-06 134522.png

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."

Omid_Motamedise
Super User
Super User

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"
If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h

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

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h

i tried to upload excel file, but couldnt find an option for it ....sorry 

jfniezink
Regular Visitor

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 ?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.