Need for Merge Query expertise

[wisdoms]

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:

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? 

[Anonymous]

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

[wisdoms]

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 · Accepted solution]

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?

[wisdoms]

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!

[wisdoms]

Let's try with these dummy data

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 🙂