cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KY_13
Regular Visitor

Trouble splitting two types of data in a single column into two columns

Dear community members,


I have this set of data of debtor ledger where on the first row it is the customer name, follow by dates (Image 1)
The second customer will begin in the same pattern, first row of customer name follow by dates.
If there any way that I can split the data where:

Column 1 will be the customer name filled in (if there are 10 transactions, then there will be 10 rows of customer name)
Column 2 will be the distinct dates.

I have manually clean the data in excel file for my desired output as shown in image 2
Hope that someone can answer this.
Many thanks.

KY_13_0-1666082085640.png

KY_13_1-1666082329826.png

 

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

Hi @KY_13 ,

 

Try this in a new blank query to follow my steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOLlHwy7dSMDQyVorViVYCEwZmugaGukaGYI6hoa6BEYxjZIrEMQByzGEcMAE3zsTUDCGK2wQjM11DAzAnFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    repBlankNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Date"}),
    addDate2 = Table.AddColumn(repBlankNull, "Date2", each try Date.From([Date]) otherwise null),
    addCustomerName = Table.AddColumn(addDate2, "Customer Name", each if [Date2] = null then [Date] else null),
    fillDownCustName = Table.FillDown(addCustomerName,{"Customer Name"}),
    filterNullDate2 = Table.SelectRows(fillDownCustName, each ([Date2] <> null)),
    remOthCols = Table.SelectColumns(filterNullDate2,{"Customer Name", "Date2"})
in
    remOthCols

 

Summary:

1) addDate2 = New column: grab value from original column if it can be converted to a date

2) addCustomerName = New column: if there's no date in our previous new column, then grab whatever's in the original column

3) The rest should be self-explanatory

 

Output:

BA_Pete_0-1666083339592.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

 

To see the example code in action, go to Power Query Home tab > New source (dropdown) > Blank Query. Once you've created that and selected it, go to the Home tab > Advanced Editor and paste all of my example code over the default code in there. This will give you a working table so you can click through each step and see what's happening.

 

To actually apply this solution to your data, here's what you ned to do:

1) To add second date column - Add column tab > Custom column. Call it 'Date2' (can change later if you want), use this calculation:

try Date.From([Date]) otherwise null

2) To add customer name column - Add column tab > Custom column. Call it 'Customer Name' and use this calculation:

if [Date2] = null then [Date] else null

 3) Fill down customer name - Select the new [Customer Name] column, go to the Transform tab > Fill (dropdown) > Down.

4) Filter out the null values from the [Date2] column.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

5 REPLIES 5
KY_13
Regular Visitor

Hi Pete,
Thanks for your reply. I have just started to learn Power BI.
Where exactly should i click to insert the formula in "Power Query Editor"?

KY_13_0-1666086219883.png

 

 

To see the example code in action, go to Power Query Home tab > New source (dropdown) > Blank Query. Once you've created that and selected it, go to the Home tab > Advanced Editor and paste all of my example code over the default code in there. This will give you a working table so you can click through each step and see what's happening.

 

To actually apply this solution to your data, here's what you ned to do:

1) To add second date column - Add column tab > Custom column. Call it 'Date2' (can change later if you want), use this calculation:

try Date.From([Date]) otherwise null

2) To add customer name column - Add column tab > Custom column. Call it 'Customer Name' and use this calculation:

if [Date2] = null then [Date] else null

 3) Fill down customer name - Select the new [Customer Name] column, go to the Transform tab > Fill (dropdown) > Down.

4) Filter out the null values from the [Date2] column.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Pete,

I used your method in a new query editor and got the formula working.
Thanks a lot for your detailed steps.

 

No problem at all. Feel free to give a thumbs-up on any posts that helped you 👍

 

Happy PBI'ing

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




BA_Pete
Super User
Super User

Hi @KY_13 ,

 

Try this in a new blank query to follow my steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOLlHwy7dSMDQyVorViVYCEwZmugaGukaGYI6hoa6BEYxjZIrEMQByzGEcMAE3zsTUDCGK2wQjM11DAzAnFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    repBlankNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Date"}),
    addDate2 = Table.AddColumn(repBlankNull, "Date2", each try Date.From([Date]) otherwise null),
    addCustomerName = Table.AddColumn(addDate2, "Customer Name", each if [Date2] = null then [Date] else null),
    fillDownCustName = Table.FillDown(addCustomerName,{"Customer Name"}),
    filterNullDate2 = Table.SelectRows(fillDownCustName, each ([Date2] <> null)),
    remOthCols = Table.SelectColumns(filterNullDate2,{"Customer Name", "Date2"})
in
    remOthCols

 

Summary:

1) addDate2 = New column: grab value from original column if it can be converted to a date

2) addCustomerName = New column: if there's no date in our previous new column, then grab whatever's in the original column

3) The rest should be self-explanatory

 

Output:

BA_Pete_0-1666083339592.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors