Power M Query

[GovindPrajapat]

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.

[lherna55]

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

[GovindPrajapat]

Thankyou so much @AlienSx  and @baghdadi62,  it worked.

[baghdadi62]

A lovely solution proposed by @AlienSx 🌷

I tested it. In a larger file, it performed better.  💪 🙏

Before:

After:

[baghdadi62]

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

[baghdadi62 · Accepted solution]

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}

[GovindPrajapat]

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

[AlienSx]

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".