Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Collapsing rows across versions when reference numbers are shared

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:

J_Baer_1-1652201128003.png

Example data from SharePoint entries

J_Baer_2-1652201314707.png

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.

 

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Please use Power Query's grouping to implement.

 

vkkfmsft_0-1652427516472.png

 

vkkfmsft_2-1652427640380.png

 

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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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 CompletedInitial Risk ScoreDate Controls CompletedControlled Risk ScoreReference#Version#
5/2/2022155/11/2022121001
nullnull5/13/202231002
5/1/202215nullnull991
nullnull5/2/20226992
nullnull5/3/20220993

 

And this is the way that I'd like the data to be displayed when creating graphs:

Date Baseline CompletedInitial Risk ScoreDate Controls CompletedControlled Risk ScoreReference#Version#
5/2/2022155/13/202231001
5/1/2022155/3/20220991

 

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.

 

vkkfmsft_0-1652427516472.png

 

vkkfmsft_2-1652427640380.png

 

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.

Anonymous
Not applicable

Oh, it looks like that'll work with a bit of extra coding in the step for that line, thank you for the help. 

PhilipTreacy
Super User
Super User

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

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors