cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
anrema06 Frequent Visitor
Frequent Visitor

Data Modeling Power Query, Operations between rows

Hello Everyone¡¡ I am new in this community and I would like in advantage for your support. 

 

My two questions are:

 

1) I'm working with a query, and I need to get the immediate value of the previous row. I have been able to do it using index columns and doing a MERGE, between the queries, however this operation is very heavy and I would like to know if there is a way to do it without doing a MERGE of queries.

image.pngimage.png

 

As you can see in this column "Location Origin which belongs to the previous row", which is comfortable for a day of DATA, however to obtain the monthly indicator it becomes terribly heavy. could you help me? with another method less heavy.

 

2) It is possible to identify in a calculated column, the number of times a value in the "SKU" column is repeated, within the same value in the column "CARGA & PAL". I explain myself better, in the column "CARGA & PAL" the strand of products is identified, and in the column "SKU" the product, what I want to know is for each pallet how many times that product has been repeated, and if it is possible to place it in a calculated column.

image.png

 

Thank you for the support.

 

Leonardo Reyes.

Trainee at Arca Continental 

Coca Cola-Company

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Nick_M New Contributor
New Contributor

Re: Data Modeling Power Query, Operations between rows

You can use an index column starting at zero with this code for a new column:

try 
List.Sum(
    List.Range(
         #"Added Index"[Total Sales],
         [Index]-1,
         1
    )
)
otherwise
0

PQ previous row value.png

 

For your other question, you can group by those columns and get a distinct count. It may not be your ideal solution, but it's at least a starting point. 

5 REPLIES 5
Nick_M New Contributor
New Contributor

Re: Data Modeling Power Query, Operations between rows

You can use an index column starting at zero with this code for a new column:

try 
List.Sum(
    List.Range(
         #"Added Index"[Total Sales],
         [Index]-1,
         1
    )
)
otherwise
0

PQ previous row value.png

 

For your other question, you can group by those columns and get a distinct count. It may not be your ideal solution, but it's at least a starting point. 

Super User
Super User

Re: Data Modeling Power Query, Operations between rows

This is a method to get the previous row that doesn't rely on merging: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po...

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




anrema06 Frequent Visitor
Frequent Visitor

Re: Data Modeling Power Query, Operations between rows

Thank you it works¡

anrema06 Frequent Visitor
Frequent Visitor

Re: Data Modeling Power Query, Operations between rows

I was trying the method to solve the second question, and the following occurred to me:

I did it with a VLOOKUP, looking for the value of the "SKU" column, and returning the value of the "index" column, those values ​​that are different is because that SKU value appeared previously. My problem is that the VLOOKUP, covers the whole range and I want it to cover only the range that covers "CARGA & PAL", that is to say that it only searches in the repeated values ​​of "CARGA & PAL", as if it were a VLOOKUP with variable range, it is possible do it in POWER QUERY?

 

image.pngimage.png

Nick_M New Contributor
New Contributor

Re: Data Modeling Power Query, Operations between rows

Finding a value between a range of other values has been much easier using DAX, in my experience.

 

Any chance you can upload some sample data?