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

[KY_13]

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.

[BA_Pete · Accepted solution]

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:

Pete

[KY_13]

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

[BA_Pete · Accepted solution]

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

[KY_13]

Hi Pete,

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

[BA_Pete]

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

Happy PBI'ing

Pete

[BA_Pete · Accepted solution]

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:

Pete