Hello,
I have an issue that I try to solve in power pivot, but I don't figure it out.
I know well Excel, power query is quite OK I would say , but I'm quite new in power pivot (However I have knowledge on SQL and on data models) .
here is my problem:
I have data that comes from my Oracle DB, and gives me like a schedule:
On a service, I have 1 vessel, that goes to a city then to another, etc... on a specific date.
When the continent change, I put a flag "Y" on the concerned location
(see file attached)
I would want to group for each service/vessel, all cities regarding the first previous city of the continent.
To test, and for the example, I have managed to do that in Excel, like in the file attached
* Source data are in Blue
* Requested calculated fields are in Green
Data are sorted by Service/vessel/date. And I have an array formula to identify the 1st previous city on the service/vessel that has the change flag = "Y". then I use a basic index formula to get the point and date.
But since I have a very big volume of data, it will not works in Excel (use of array formula...).
So I wanted to do the same in power pivot (or power query...)
1/ First, (for information) I have tried to do it in power query, (to summarize) by using function Table.Max in a function, and it works :
GetOpsData = (table as table, service as text, vessel as text, aDate as datetime) => Table.Max(Table.SelectRows(table, each [SERVICE_NO]=service and [VESSEL]=vessel and aDate>=[DATE] and [CHANGE]="Y"), "DATE")
By applying the function on each line, this give me a record for each line, and by expanding each record, I get the CITY and the DATE wanted. This way, for each line, I have the data of the 1st city of the continent where the vessel is passed just before.
But it only works also for a limited amount of data. When I put several thousand rows, the data refresh freezes, because of the function 'GetOpsData'
So impossible to use this solution.
2/ So I wanted to try with power pivot, since at the end I wanted to use power pivot to analyze data.
But:
* First I get data from ORACLE, with power query (because in reality I do some data transformation), and data are sorted by service/vessel/date, this is very important.
Pb when data are added to the data model and imported in power pivot, data are NOT sorted! And I don't manage to sort them in power pivot table by service/vessel/date
* Then even if it would be sorted, in all cases, I don't manage to use the PowePivot functions to create calculated fileds and do the same as in my excel or power query solution
I have tried CALCULATE() function with MAX function, FILTERS(), tried EARLIER() function too (but since data are not sorted as desired, I suppose it cannot work), tried TOPN also, nothing works.
Basically I just need to do in Power pivot an approximate VLOOKUP on the current table, and based on several criteria (like done in my power query solution :get [SERVICE_NO]=service and [VESSEL]=vessel and aDate>=[DATE] and [CHANGE]="Y")
I hope I'm clear and someone will be able to help me, because I'm stuck
Perhaps there is also another way to do what I want I don't know.
Thank you in advance for your valuable help.
I have an issue that I try to solve in power pivot, but I don't figure it out.
I know well Excel, power query is quite OK I would say , but I'm quite new in power pivot (However I have knowledge on SQL and on data models) .
here is my problem:
I have data that comes from my Oracle DB, and gives me like a schedule:
On a service, I have 1 vessel, that goes to a city then to another, etc... on a specific date.
When the continent change, I put a flag "Y" on the concerned location
(see file attached)
I would want to group for each service/vessel, all cities regarding the first previous city of the continent.
To test, and for the example, I have managed to do that in Excel, like in the file attached
* Source data are in Blue
* Requested calculated fields are in Green
Data are sorted by Service/vessel/date. And I have an array formula to identify the 1st previous city on the service/vessel that has the change flag = "Y". then I use a basic index formula to get the point and date.
But since I have a very big volume of data, it will not works in Excel (use of array formula...).
So I wanted to do the same in power pivot (or power query...)
1/ First, (for information) I have tried to do it in power query, (to summarize) by using function Table.Max in a function, and it works :
GetOpsData = (table as table, service as text, vessel as text, aDate as datetime) => Table.Max(Table.SelectRows(table, each [SERVICE_NO]=service and [VESSEL]=vessel and aDate>=[DATE] and [CHANGE]="Y"), "DATE")
By applying the function on each line, this give me a record for each line, and by expanding each record, I get the CITY and the DATE wanted. This way, for each line, I have the data of the 1st city of the continent where the vessel is passed just before.
But it only works also for a limited amount of data. When I put several thousand rows, the data refresh freezes, because of the function 'GetOpsData'
So impossible to use this solution.
2/ So I wanted to try with power pivot, since at the end I wanted to use power pivot to analyze data.
But:
* First I get data from ORACLE, with power query (because in reality I do some data transformation), and data are sorted by service/vessel/date, this is very important.
Pb when data are added to the data model and imported in power pivot, data are NOT sorted! And I don't manage to sort them in power pivot table by service/vessel/date
* Then even if it would be sorted, in all cases, I don't manage to use the PowePivot functions to create calculated fileds and do the same as in my excel or power query solution
I have tried CALCULATE() function with MAX function, FILTERS(), tried EARLIER() function too (but since data are not sorted as desired, I suppose it cannot work), tried TOPN also, nothing works.
Basically I just need to do in Power pivot an approximate VLOOKUP on the current table, and based on several criteria (like done in my power query solution :get [SERVICE_NO]=service and [VESSEL]=vessel and aDate>=[DATE] and [CHANGE]="Y")
I hope I'm clear and someone will be able to help me, because I'm stuck
Perhaps there is also another way to do what I want I don't know.
Thank you in advance for your valuable help.