Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙏
Solved! Go to 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/
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,
@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/
Thank a lot, @amitchandak
I can solved it.
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.
@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,
Hi,
I used below one.
Date.AddDays([Date],(-1))
Thanks,
Shanthakumar.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |