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
wisdoms
New Member

Need for Merge Query expertise

Hi all, 

 

I'm struggling with a PBI issue that comes from sourced database (that can't be edited on purpose). This is a simplified sketch of my Model View:

Screenshot 2023-03-29 at 09.45.22.png

Basically I need to list all Feature IDs related to a single Story ID. Of course, this is possible via Mapping table, but I must "append" on the same Visual (e.g. Table) also those Feature IDs grouped by Label ID, since they are definitely related to same Story ID as well.

At the moment I can only slice either by Story ID or by Label ID separately.

 

I've already tried with some LOOKUP and some new relationships on Model View, but very often I end up into some circularity. So no way. From Transform data, I do assume that a Merge Queries as New might consolidate all fields I need on the same table.

 

Has anyone experienced similar situation? 

1 ACCEPTED SOLUTION
ChielFaber
Super User
Super User

While it's difficult to say exactly I think to following might over a solution:

 

In the powerquery editor start with the table Story. Through the user interface select merge as new and merge the Story table with the Mapping table using a left outer join. This combines all the available story id's in the story table with the corresponding story_id in the mapping table. 

 

The next step would be to merge this newly created table with the features table. You could do this using a normal merge (again through the UI). This merges the table in the same query code or you could again us Merge queries as new. When you use the last function you could consider disabling the query load of the first merge.

 

When you use another left join between the newly created table and the features table you get al the story_id out of the story table with the all the corresponding feature_id's and Label names.

 

If this isn't the solution could you then provide some dummy data to further explain the tables content?

View solution in original post

5 REPLIES 5
watkinnc
Super User
Super User

Not quite sure I understand completely, but could you just make a one column table of unique Story IDs to filter your Story table, and a one column table of unique Label IDs to filter the Label table? Then you would hide any other Label ID and Story ID columns in the model.

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

thanks for replying. With this method I cannot have one Feature ID table, since 2x slicing methods on same target (Features) are not possible. I'm not sure I can properly state this problem in plain english, but I'm trying to filter twice the same feature set by STory ID, based on the information/relationships of 2x other tables (Mapping && Labels). With the current query this is not possible.

ChielFaber
Super User
Super User

While it's difficult to say exactly I think to following might over a solution:

 

In the powerquery editor start with the table Story. Through the user interface select merge as new and merge the Story table with the Mapping table using a left outer join. This combines all the available story id's in the story table with the corresponding story_id in the mapping table. 

 

The next step would be to merge this newly created table with the features table. You could do this using a normal merge (again through the UI). This merges the table in the same query code or you could again us Merge queries as new. When you use the last function you could consider disabling the query load of the first merge.

 

When you use another left join between the newly created table and the features table you get al the story_id out of the story table with the all the corresponding feature_id's and Label names.

 

If this isn't the solution could you then provide some dummy data to further explain the tables content?

Hi ChielFaber, 

 

your solution works adding and an "Aggregate" in the final step (otherwise Feature IDs from Mapping land always on a different column). However I got there where I wanted. Thanks!

 

Feature List results.PNG

Let's try with these dummy data

Screenshot 2023-03-29 at 13.50.51.png

 

So clicking S1 as Story ID, I'd like to get all the feature IDs by Mapping (aka F30, F31) AND llabelled by "Blue" (aka F10, F11, F19, etc.).

 

Maybe now the problem is more defined 🙂

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