How to cross reference two queries and show common results?

Power Query
1

Hello,
I know what I am trying to achieve but not really sure how to really begin to solve involves the following:

  • I have a Data query that has lots of rows, but one column specifically is the “License Type”.
  • The license types themselves are referenced in separate “Bundle” queries that contain the Bundle name and the license types.

In my PBIX file, I have included the sample queries and the desired final table. But I’ll explain what I am looking for here. In my final table, I am wanting to cross reference the Data query with the Bundle query and produce a column that shows all the License Types that were used in the Data query.

I’m having trouble figuring out where to begin because I know that there will be some License Types that are included in the bundle, but since they weren’t actually used, I want to omit them from the final “License Actually Used” column in my example table.
License-Bundle.pbix (31.7 KB)

I hope I explained that correctly. Any help would be much appreciated.

2

Hi @jsonify,

See if this works for you.
Copy this script into a new blank query, in your supplied sample file. Important to note, I made sure your column names in both Bundle tables were named the same.

let
    Source = #"Bundle 1" & #"Bundle 2",
    GetData = Table.Group(Source, {"Bundle Name"}, {{"License per Bundle", each Table.RowCount(_), Int64.Type}, {"Licenses", each Text.Combine( List.Intersect( { _[License], Data[License Name] }, Comparer.OrdinalIgnoreCase ), "#(cr)"), type text }})
in
    GetData

.
Creates this output table.

image

I hope this is helpful

3

That seems to have worked for the SAP bundle, but for some reason, the Web&Multimedia bundle shows blank for me. But it worked for you.

image
image816×345 90.7 KB

4

So did you update the column name?
image

5

You’re a genius! The column name was different in the “Bundle 2” query. But after updating to match, it worked perfectly.

Thank you so much!

1 Like