Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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. 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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. 

Anonymous
Not applicable

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

Anonymous
Not applicable

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?

Anonymous
Not applicable

Thank you it works¡

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.