Forum Discussion
Power M Query
I want to add a custom column in my power bi dataset whare i want if the coloumn "A" is true the value should be 0 else the value should be the difference of previous records "Datetime" coloumn value and the current records "Datetime" Coluumn value. how can i do it in power bi using M Language Query.
Hi,
First, add an index column to the table starting from zero.Next, write a formula like the following to check the value of column A and, if it evaluates to true, subtract the date value of the current row from the date value of the previous row:
⭐Please note that columns in Power Query are the list type, and you can access their values through the item number or index number by using curly braces {} at the end of their names.Imagine that we have a column named A and whant to acces to the 5nth row of that column, the code is: [a]{5}
7 Replies
Hi,
First, add an index column to the table starting from zero.Next, write a formula like the following to check the value of column A and, if it evaluates to true, subtract the date value of the current row from the date value of the previous row:
⭐Please note that columns in Power Query are the list type, and you can access their values through the item number or index number by using curly braces {} at the end of their names.Imagine that we have a column named A and whant to acces to the 5nth row of that column, the code is: [a]{5}
Hi baghdadi62 ,
I tried it and it worked for me. I have successfully achived what i was looking for. But After applying queries the data started to load and from last 7 hours it is being load. It is not coming out of this process. I have 100000 records in my data. The load has reached 16 GB and still Going on. Kindly suggest a way to resolve this issue.
BA_Pete slorin AlienSx ronrsnfld collinsg Please help if you know the reason behind this situation and the solution for the same
Hi, GovindPrajapat lets try smth simple first. You need to add new step
whatever = List.Buffer(#"Added Index"[oDate])
and replace #"Added Index"[oDate] phrase in your Table.AddColumn step with "whatever".
A lovely solution proposed by AlienSx 🌷
I tested it. In a larger file, it performed better. 💪 🙏
Before:
After:
Probably its inevitable but read these articles:
https://medium.com/codex/how-fast-is-fast-data-load-in-power-query-704e48f1636f
https://medium.com/codex/analysing-10-million-rows-in-excel-b1d6e0f5d2ba
Thankyou so much AlienSx and baghdadi62, it worked.
Hi¡¡
I'm trying to resolve the same kind of problem.
I have this formula :
but in the end the column gives me an error, I changes the date to text, but it gives me an error anyway.
Can you help me please. @AlienSx and @baghdadi62
