Forum Discussion
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.
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.
Thank you for the support.
Leonardo Reyes.
Trainee at Arca Continental
Coca Cola-Company
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 0For 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
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 0For 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.
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-power-query-or-power-bi/
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?
