Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Lead function, with partition by and order by in Power Query

Hello guys, i need help to replicate the same function i have in sql to power query. I need to bring the result of the previous row of the column "ORGANIZATION_UNIT_ID" partitioned by a cluster which...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
artpil's avatar
artpil
Resolver II
4 years ago

Hi Vijay,

This is great code. Can you explain what's going on in this code?

List.Generate(()=>[x=null,i=0], each [i]<List.Count(ListOfValues), each [i=[i]+1, x=(if ListOfIDs{i}=ListOfIDs{[i]} then ListOfValues{[i]} else null)], each [x])

What I undestood is:

()=>[x=null,i=0] - this creates record object with values x=null and i=0

each [i]<List.Count(ListOfValues) - this checks if iteration is smaller then number of items in list

each [i=[i]+1, x=(if ListOfIDs{i}=ListOfIDs{[i]} - this creates record with column i equal i + 1 and column x - here I don't understand what is happening. ListOfIDs{i} means get element i from list ListOfIDs but what does it does ListOfIDs{[i]} ?

last part returns x calculated earlier.

Thanks in advance for your explanantion.

Artur

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

i = [i]+1, hence in ListOfIDs{i}=ListOfIDs{[i]}, I can also use ListOfIDs{i}=ListOfIDs{i-1} => I am checking wether current value of list is = previous value of list or not. If it is equal, it means same group continues. If it is unequal, then a new group has started. If a new group is started, then result should be null. If within same group, then value is ListOfValues{[i]} where [i] means i-1. So I could have written it ListOfValues{i-1} as well.