Forum Discussion
Copying column value until value changes then new value
I have this system that outputs log files but it does so in an infuriating way. The data pretty much looks like this (with more columns)
| Call ID | From | To | Result | Date/Time |
| 11111222 | 614-111-1212 | 614-555-1212 | Queue 1 | 12/31/2024 10:03:00 |
| 614-111-1212 | 614-555-1212 | IVR OPT 1 | 12/31/2024 10:03:00 | |
| 614-111-1212 | 614-555-1212 | voicemail | 12/31/2024 10:03:00 | |
| 11111223 | 614-222-1212 | 614-555-1212 | Queue 1 | 12/31/2024 10:05:35 |
| 614-222-1212 | 614-555-1212 | IVR OPT 1 | 12/31/2024 10:05:35 | |
| 614-222-1212 | 614-555-1212 | voicemail | 12/31/2024 10:05:35 |
So, essentially the CSV is sort of pivoted, I guess. I don't know what you call it and, of course, this is only sample information. What I need is some sort of power Query script or something else which will duplicate the 11111222 to the other two blank lines and then when it hits 11111223 it then copies that 1111223 to the next two blank lines, etc.
There may be 3 blanks, 5 blanks depending on the routing, or any other number of blanks. This way I can report on the routing of these calls.
What is possible? Do I automate it in excel before sending the CSV to Power BI or do something in Power BI in Power Query?
5 Replies
Thanks, I had tried fill down but I didn't know about the nulls. They were "blanks" so I converted them to nulls and i'm in business. Thanks.
Just right click on the column call ID, and then select fill down.
This was the solution but Watkinnc was just a bit closer because I tried this and it didn't work. I had to convert them to nulls first.
Hi Thomas_MedOne,
We sincerely appreciate your inquiry through the Microsoft Fabric Community Forum.
Please find below the process to use Power Query for filling down the Call ID values:
- Load the Text/CSV file data into Power BI.
- After loading the data, click on Transform Data to open the Power Query Editor.
- Inspect the Call ID column, where blank rows need to be filled. Replace the blank values with Null if blank cells are not already recognised as nulls.
- Select the Call ID column, navigate to the Transform tab, click on Fill, and choose Down. This will fill all blank cells in the column with the last non-blank value above them.
- Preview the data to ensure that all blank rows under the Call ID column have been correctly filled with the appropriate values.
- Click on Close & Apply in the Home tab of the Power Query Editor to load the data back into Power BI with the filled Call ID values.
Please find the attached screenshot and PBIX file for your reference:
If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.
Thank you.
