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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors