relation comma seperated fields

[melvin_pbi]

Hello All,

I'm quiet new to PowerBi and looking for some guidance on the following case:

I have 2 textfiles:

Groups:

GroupID,GroupDisplayName
554dc39f-a4a2-422d-b298-c3eb1fdb66fe,GroupName1
4d502719-f081-47c7-a263-b51a9699408e,GroupName2
1f452b67-77b9-43cc-aa52-82fb931b4546,GroupName3
36c718d4-3ee5-42c6-a524-6c5461d61e93,GroupName4

CAPolicies

CaID;CaDisplayName;CaUserGroupsInclude
2ed83e55-69b4-4bd7-be16-126e95539814;MAM Mobile Devices;754dc39f-a4a2-422d-b298-c3eb1fdb66fe,86c718d4-3ee5-42c6-a524-6c5461d61e93
642df832-fb53-40ed-9f73-2665707a9520;MAM Workstations;8d502719-f081-47c7-a263-b51a9699408e,3f452b67-77b9-43cc-aa52-82fb931b4546,86c718d4-3ee5-42c6-a524-6c5461d61e93
d6425f5c-a1c9-4627-b762-4b06de0bdc34;_CA03-Exchange Online: Block legacy authentication;754dc39f-a4a2-422d-b298-c3eb1fdb66fe

I related the table columns GroupID & CaUserGroupsInclude 

Now I end up with the following result where the cell with a single id shows the displayname of the group correctly:

Whats the best way to do this with the field containing multiple values, so the column GroupDisplayName also get filled with pultiple values?

Thanks!

[HotChilli]

The relationship won't work if you store the data in that format but you can probably create a table visual and create 2 measures using the CONCATENATEX keyword to get that output in a visual

[HotChilli]

In power query, if you split the column CaUserGroupsInclude(split column by delimiter, on ',' using split to rows)

the relationship will work.

You might have to Clean or Trim the column if there are extra spaces.

[melvin_pbi]

Tnx for your reply!

The result is this:

Would it be possible to have the groupnames in the same field per policy?
Like this:

Ca Policy	GroupIDs	GroupDisplayNames
CA Policy 1	554dc39f-a4a2-422d-b298-c3eb1fdb66fe,36c718d4-3ee5-42c6-a524-6c5461d61e93	GroupName4 GroupName1
CA Policy 2	4d502719-f081-47c7-a263-b51a9699408e,1f452b67-77b9-43cc-aa52-82fb931b4546,36c718d4-3ee5-42c6-a524-6c5461d61e93	GroupName3 GroupName4 GroupName2
CA Policy 3	554dc39f-a4a2-422d-b298-c3eb1fdb66fe	GroupName1