Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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"?
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
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.