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 !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
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 !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
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
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!