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

How to use Lookupvalue in M Query from same table

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 :

Siriwat_Lukin_0-1627457362406.png

 

and the data that i want in yellow column :

Siriwat_Lukin_1-1627457655281.png

 

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 🙏

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

7 REPLIES 7
ashanthakumar
Helper II
Helper II

hi,

 

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

 

Thanks,

Shanthakumar.

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
Super User
Super User

@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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thank a lot, @amitchandak 

 

I can solved it.

Siriwat_Lukin_0-1627462276565.png

 

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

Hi,

 

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

 

Thanks,

Shanthakumar.

@ashanthakumar ,

 

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,

Hi,

 

       I used below one.

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

Thanks,

Shanthakumar.

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!