Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey guys,
I currently scan through various folders and pull in a series of spreadsheets that are generated each month with the latest KPI data. The problem arises during the reporting period when we have incomplete data. The reports look terrible because there are a bunch of blanks for the latest reporting period. To get around this, I created several queries to transform the data to only pull in the latest available data for that KPI:
This is definitely not efficient and there has to be a better way to do it. I would like to simplify the process because a new excel format is being introduced into the data collection effort which is complicating the queries. How can I simplify this process?
Thanks!
Hi @More_BI,
Is ImkeF suggestion helpful to your scenario? If so, please kindly mark the corresponding reply as an answer so that it can benefit others having similar requirements. If you still have any question, please feel free to ask.
Thanks,
Yuliana Gu
Well, not entirely. It is the same way I'm doing it now. It's just many, many steps that I was hoping to reduce. Not a huge deal since it works, but I would have loved to simplify it somehow.
To clarify, my step #5 is the what was described in the response above. I didn't use that exact syntax but more-or-less the same logic where I created a column to check to see if there was a value in the Latest.[Column Name] then set to TRUE. Then I created a third set of columns with the latest available value (keying off my new column to tell me which version to use). I'm not too happy with this method, but again, it works.
You can easily select only those rows of a table who have the latest value in the [Period] like this:
Table.SelectRows(Table, each [Period]=List.Max(Table[Period]))
This assumes that the natural sort order of the values in Column "Period".
If that's not the case, you can check if rows are returned once you select the latest period (and take those then) and if it is empty, filter the previous period like this:
if Table.RowCount(Table.SelectRows(Table, each [Period]=LastPeriod)) >0 then Table.SelectRows(Table, each [Period]=LastPeriod)
else Table.SelectRows(Table, each [Period]=PreviousPeriod)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |