cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Cassis Frequent Visitor
Frequent Visitor

Help needed with data preparation (using M)

Hey ppl!

I've received a new task and need to learn M fast.

Problem is, I have a "simple" problem to solve but didn't learn enough M yet to solve it in a timely manner.

Hope you guys can help.

 

I have a table called "Update" with a single column "When" and single row containing one date.

Like this:


TABLE Update
When
28/02/2020

 

This date is dynamic, obtained by a query.

 

I have a second table "Water_Consumption" with a date column "Reading" and a column named "m3" formatted as number (integer). Like this:


TABLE Water_Consumption
Reading                m3
22/12/2019          210
22/01/2020          208
22/02/2020             0
22/03/2020             0
22/04/2020             0

 

My problem:
I need to change 0 to null when the date is greater than the date in "When".

So in the current example, my Water_Consumption table would look like:


TABLE Water_Consumption
Reading          m3
22/12/2019     210
22/01/2020     208
22/02/2020        0
22/03/2020     null
22/04/2020     null

 

This is as far as I could go, it works but requires me a manual update every month:

 

Table.ReplaceValue(#"Previous_step", each [m3], each if [Reading] <= #date(2020, 2,28) then [m3] else null, Replacer.ReplaceValue, {"m3"} )

 

Problem is, I need to make it update automatically so my current "solution" doesn't cut it.

 

Can you guys please advise?

 

Thanks in advance!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Help needed with data preparation (using M)

Hi @Cassis,

 

it seems like you are taking on the advanced editor in Power Query. Perhaps an easier way to do this is to first create a new column with the value from Update, and then create another column according to the logic you have. Once that is completed you can remove the original m3-column and the When-column.

 

If you still want to do this in Advanced editor, I think this will work for you:

= Table.ReplaceValue(#"Previous step", each [m3], each if [Reading] <= List.First(Update[When]) then [m3] else null, Replacer.ReplaceValue, {"m3"} )

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

View solution in original post

2 REPLIES 2
Super User III
Super User III

Re: Help needed with data preparation (using M)

Hi @Cassis,

 

it seems like you are taking on the advanced editor in Power Query. Perhaps an easier way to do this is to first create a new column with the value from Update, and then create another column according to the logic you have. Once that is completed you can remove the original m3-column and the When-column.

 

If you still want to do this in Advanced editor, I think this will work for you:

= Table.ReplaceValue(#"Previous step", each [m3], each if [Reading] <= List.First(Update[When]) then [m3] else null, Replacer.ReplaceValue, {"m3"} )

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

View solution in original post

Cassis Frequent Visitor
Frequent Visitor

Re: Help needed with data preparation (using M)

Thanks mate, your solution solved the issue!

 

Also thanks for your suggestion, I believe it would be an easier way to solve the problem but I was afraid the impact in performance would hurt the model as a whole - that's why I opted to use the editor and dedicate an effort to learn M.

 

 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors