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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors