Hi,
Bit of a multi part question as new to PQ and PBI. I have an initial table of data storing audits by location, type, date and the score awarded. There are lots of things I want to eventually pull out/ display from the data but two main things to focus on:
1) Finding the most recent score by location/ type and date (and subsequently being able to count the instances - note the scores entered are generally text like red/ amber /green for eg.)
2) Being able to calculate audit window for each location based on last audit date (by type) compared to a defined "window" of when the next one could happen.
If I try to apply logic like Access, I would build tables to store all things I might want to filter by and where "one instance" occurs, so location table, audit type table, a table that takes the audit types and assigns what scores are possible might be another(?), etc, However I thought part of the benefit of Power Query was to almost do this for you....
So...my first attempt at focus point 1 (finding most recent) was to take my master data and transform it into a table of one instance of site and audit type (as a joined field) The table shows both these as separate fields in the table as well. It then uses the joined field to find the max date from the master data for each Location+AuditType instance. It then uses the Location+AuditType AND the max date returned to lookup in the master data and return the corresponding score. This works lovely
However, Power BI doesn't agree at the moment. It cannot see the site name and audit type as two distinct fields so I can't filter the results of the count independently.
Would I be right in thinking two tables with these details stored as unique values and then linked to the "most recent" query be the way to resolve? Would it be the CORRECT way to resolve? Are there other options?
Thanks in advance for your expertise. Please try and keep jargon free (or at least define the jargon) so this learner can learn
Bit of a multi part question as new to PQ and PBI. I have an initial table of data storing audits by location, type, date and the score awarded. There are lots of things I want to eventually pull out/ display from the data but two main things to focus on:
1) Finding the most recent score by location/ type and date (and subsequently being able to count the instances - note the scores entered are generally text like red/ amber /green for eg.)
2) Being able to calculate audit window for each location based on last audit date (by type) compared to a defined "window" of when the next one could happen.
If I try to apply logic like Access, I would build tables to store all things I might want to filter by and where "one instance" occurs, so location table, audit type table, a table that takes the audit types and assigns what scores are possible might be another(?), etc, However I thought part of the benefit of Power Query was to almost do this for you....
So...my first attempt at focus point 1 (finding most recent) was to take my master data and transform it into a table of one instance of site and audit type (as a joined field) The table shows both these as separate fields in the table as well. It then uses the joined field to find the max date from the master data for each Location+AuditType instance. It then uses the Location+AuditType AND the max date returned to lookup in the master data and return the corresponding score. This works lovely
However, Power BI doesn't agree at the moment. It cannot see the site name and audit type as two distinct fields so I can't filter the results of the count independently.Would I be right in thinking two tables with these details stored as unique values and then linked to the "most recent" query be the way to resolve? Would it be the CORRECT way to resolve? Are there other options?
Thanks in advance for your expertise. Please try and keep jargon free (or at least define the jargon) so this learner can learn
