Forum Discussion
PQ Transformation
Dear All,
I have the following table in PowerQuery:
| Date | Value |
| 15/01/2025 | null |
| 14/01/2025 | null |
| 13/01/2025 | null |
| 12/01/2025 | null |
| 11/01/2025 | null |
| 10/01/2025 | null |
| 09/01/2025 | null |
| 08/01/2025 | null |
| 07/01/2025 | null |
| 06/01/2025 | null |
| 05/01/2025 | null |
| 04/01/2025 | null |
| 03/01/2025 | null |
| 02/01/2025 | null |
| 01/01/2025 | null |
| null | 766,97 |
| null | 765,48 |
| null | 613,73 |
| null | 1453,6 |
| null | 1100,58 |
| null | 1000,02 |
| null | 950,54 |
| null | 746,31 |
| null | 924,9 |
| null | 669,48 |
| null | 844,7 |
| null | 1280,44 |
| null | 927,59 |
| null | 1231,85 |
| null | 634,31 |
| 15/02/2025 | null |
| 14/02/2025 | null |
| 13/02/2025 | null |
| 12/02/2025 | null |
| 11/02/2025 | null |
| 10/02/2025 | null |
| 09/02/2025 | null |
| 08/02/2025 | null |
| 07/02/2025 | null |
| 06/02/2025 | null |
| 05/02/2025 | null |
| 04/02/2025 | null |
| 03/02/2025 | null |
| 02/02/2025 | null |
| 01/02/2025 | null |
| null | 757,68 |
| null | 701,98 |
| null | 1260,99 |
| null | 360,49 |
| null | 867,07 |
| null | 860,66 |
| null | 978,51 |
| null | 1077,89 |
| null | 1084,86 |
| null | 941,76 |
| null | 775,19 |
| null | 858,75 |
| null | 654,13 |
| null | 1549,75 |
| null | 1145,56 |
My desired result is the following:
| Date | Value |
| 15/01/2025 | 766,97 |
| 14/01/2025 | 765,48 |
| 13/01/2025 | 613,73 |
| 12/01/2025 | 1453,6 |
| 11/01/2025 | 1100,58 |
| 10/01/2025 | 1000,02 |
| 09/01/2025 | 950,54 |
| 08/01/2025 | 746,31 |
| 07/01/2025 | 924,9 |
| 06/01/2025 | 669,48 |
| 05/01/2025 | 844,7 |
| 04/01/2025 | 1280,44 |
| 03/01/2025 | 927,59 |
| 02/01/2025 | 1231,85 |
| 01/01/2025 | 634,31 |
| 15/02/2025 | 757,68 |
| 14/02/2025 | 701,98 |
| 13/02/2025 | 1260,99 |
| 12/02/2025 | 360,49 |
| 11/02/2025 | 867,07 |
| 10/02/2025 | 860,66 |
| 09/02/2025 | 978,51 |
| 08/02/2025 | 1077,89 |
| 07/02/2025 | 1084,86 |
| 06/02/2025 | 941,76 |
| 05/02/2025 | 775,19 |
| 04/02/2025 | 858,75 |
| 03/02/2025 | 654,13 |
| 02/02/2025 | 1549,75 |
| 01/02/2025 | 1145,56 |
What I've done is matching every date with its value by eliminating the first 15 rows of nulls in the Value column.
I don't know how to do this in powerquery..
I am not a AI response.
Please try this method which i have tested ...
Reference yourdata to create a new table called "dates"
Remove any rows from "dates" that have nullsThen add a index column
Then do the same kind of thingsto create a "values" tables
So you now have 2 tables
Merge the tables using the index to create another table
Expand the table
Congratultaions ... you have your answer
Merge the tables
5 Replies
I am not a AI response.
Please try this method which i have tested ...
Reference yourdata to create a new table called "dates"
Remove any rows from "dates" that have nullsThen add a index column
Then do the same kind of thingsto create a "values" tables
So you now have 2 tables
Merge the tables using the index to create another table
Expand the table
Congratultaions ... you have your answer
Merge the tables
Hi powerbricco ,
You can achieve your desired result in Power Query by filtering out the rows where the "Value" column is null. Here’s how you can do it:
- Open Power Query Editor.
- Select the "Value" column.
- Go to the top menu and click on "Remove Rows" > "Remove Blank Rows".
- Alternatively, right-click on the column header and choose "Remove Empty".
- If you want more control (for example, if sometimes there are other nulls you want to keep), you can use the filter dropdown on the "Value" column and uncheck "(null)" to filter them out.
After applying this step, only the rows with actual values in the "Value" column (and their corresponding dates) will remain, just like your desired result.
If you want the equivalent M code:
m= Table.SelectRows(YourTableName, each [Value] <> null)Replace "YourTableName" with the actual name of your previous step.
Let me know if you need a more detailed step-by-step or further customization!
translation and formatting supported by AIIt does not work cause if i remove the nulls in Value column, it deletes the row with the dates in Date column...
Thanks for your feedback! If removing nulls in the Value column deletes the rows with dates in the Date column as well, it’s because those date values are only present in the upper rows, and the rest are null.
To fix this, you should first "Fill Down" the Date column so that each row has a date. Then you can filter out the rows where Value is null.Here’s how:
- Select the Date column in Power Query.
- Go to Transform > Fill > Down.
This will fill the date down into all the null cells. - Now remove the rows where Value is null.
M code example:
mFilledDown = Table.FillDown(YourPreviousStep, {"Date"}), Result = Table.SelectRows(FilledDown, each [Value] <> null)
Let me know if you need a more detailed step-by-step!
translation and formatting supported by AI
Are you just repling with AI generated answers?
You seem to copy and paste answer to previous similare question which do not work for the current question being asked. 😀😀😀
