PQ Transformation

[powerbricco]

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..

[speedramps]

@burakkaragoz 

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.  😀😀😀

[speedramps · Accepted solution]

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 nulls

Then 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

[burakkaragoz]

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:

1. Open Power Query Editor.
2. Select the "Value" column.
3. Go to the top menu and click on "Remove Rows" > "Remove Blank Rows".
   • Alternatively, right-click on the column header and choose "Remove Empty".
4. 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 AI

[powerbricco]

It does not work cause if i remove the nulls in Value column, it deletes the row with the dates in Date column...

[burakkaragoz]

@powerbricco ,

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:

1. Select the Date column in Power Query.
2. Go to Transform > Fill > Down.
   This will fill the date down into all the null cells.
3. Now remove the rows where Value is null.

M code example:

m

FilledDown = 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