Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm a little new to the whole of PowerBI/PowerQuery, but I'm hopeful that this is possible. I'm pulling data from multiple Sharepoint lists built off an online form in order to display the data on a published webpage, so most of it has to be converted into a format that these graphs can work with.
Background: The form makes it possible to enter the same data multiple times, because the metrics are updated through itterative testing (the first time you evaluate it, you get a score, enter it in, then come back, enter the same reference number and document changes, then score it again). The graphs don't care about this itterative testing, just the pre and post score, so ultimately each reference number gets one line, and the "first" and "last" version should be displayed as pre and post scores in the graph. The archived data that my graphs are based on was entered this way, but now the new data needs to be displayed this way as well.
Code: I created code which shows all entries from Sharepoint, and a separate "list" with index numbers, then narrows the list down to "just reference numbers with multiple entries". I want to use this list to identify reference numbers with multiple entries, combine the first and last entry (min and max version number) onto one line, and delete all the other rows. I would normaly do this with a for loop, but that doesn't seem possible in PowerBI using rows. It would be something like this:
For each x in "DuplicateList"
1. Table.SelectRows(#"", each Text.Contains([#"Reference#"], DuplicateList[#"Reference#"](x)))
2. min(version) columns(y) = max(version) columns(z)
3. Remove all rows with version>1
4. Clear selected rows, repeat
From testing out the code I have, I'm aware that each individual step is possible, but the issue appears to be creating a the underlying for loop function. Does anyone have a suggestion for how to solve this?
Example of DuplicateList:
Example data from SharePoint entries
I won't be able to provide a direct link to the source or the code due to some copyright/onfidentiality issues, but I can attempt to recreate it without the link to the sharepoint if it becomes necessary.
Solved! Go to Solution.
Hi @Anonymous ,
Please use Power Query's grouping to implement.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I'm not sure what the best format would be to send it, but this is an example of the way the data is input for the model:
Date Baseline Completed | Initial Risk Score | Date Controls Completed | Controlled Risk Score | Reference# | Version# |
5/2/2022 | 15 | 5/11/2022 | 12 | 100 | 1 |
null | null | 5/13/2022 | 3 | 100 | 2 |
5/1/2022 | 15 | null | null | 99 | 1 |
null | null | 5/2/2022 | 6 | 99 | 2 |
null | null | 5/3/2022 | 0 | 99 | 3 |
And this is the way that I'd like the data to be displayed when creating graphs:
Date Baseline Completed | Initial Risk Score | Date Controls Completed | Controlled Risk Score | Reference# | Version# |
5/2/2022 | 15 | 5/13/2022 | 3 | 100 | 1 |
5/1/2022 | 15 | 5/3/2022 | 0 | 99 | 1 |
I'm specifically trying to do it this way because the metrics only care about relating the first and last entry, but anyone is able to add a new entry at any time, so I can't think of a better way to aggregate the data.
Hi @Anonymous ,
Please use Power Query's grouping to implement.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Oh, it looks like that'll work with a bit of extra coding in the step for that line, thank you for the help.
Hi @Anonymous
I'm not clear on what exactly the final result you want it, can you provide an example?
You can effect loops in PQ using functions like List.Transform and List.Accumulate, depends on what you want to do.
Please also provide sampel data, preferably in a file, or at least as a table.
Regards
Phil
Proud to be a Super User!