How to use Lookupvalue in M Query from same table

[Siriwat_Lukin]

Hello,

I am Siriwat, Beginner for M language of Power BI.

I want to create energy report from Electrical meter that accumulate data as below :

and the data that i want in yellow column :

I can solve it in DAX function by create column with lookupvalue(table : meter1,table : date,table: date+1) and meter2 also.

but i can't find solution with M Query in Transform data.

So if I want to use this function in M Query, How i will do ?

Thank in advance 🙏

[Anonymous]

hi,

       is the newmeter1 and new meter2 coming from another table ?.

Thanks,

Shanthakumar.

[Siriwat_Lukin]

Hi,

No it's come from column meter1 and meter2. 

Example (meter1),

250 (from 02/01/2021) - 200 (from01/01/2021) = 50 (show in new meter1)

Thank you,

[amitchandak · Accepted solution]

@Siriwat_Lukin , You have tp create a table,  duplicate or copy . Then create column with 

new date = Date -duration(1,0,0,0) ,

Merge both on the date and new date; then use the values from two table to create these columns

refer if needed

https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/

[Siriwat_Lukin]

Thank a lot, @amitchandak 

I can solved it.

after that i will use minus for create column that i want

[Anonymous]

Hi,

  can you give the new date formula ?. I tried newdate= Date - duration(1,0,0,0). it is not working.

Thanks,

Shanthakumar.

[Siriwat_Lukin]

@Anonymous ,

I found same your problem.

I solved by change data type of  column date to "Whole Number" and add column by custom by use formula >> date-1.

after that change data type back to "Date".

Thank you,

[Anonymous]

Hi,

       I used below one.

            Date.AddDays([Date],(-1))

Thanks,

Shanthakumar.