Forum Discussion

melvin_pbi's avatar
melvin_pbi
New Member
4 years ago

relation comma seperated fields

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!

 

 

 

 

 

 

Need Help

3 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    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's avatar
      melvin_pbi
      New Member
      4 years ago

      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 PolicyGroupIDsGroupDisplayNames
      CA Policy 1554dc39f-a4a2-422d-b298-c3eb1fdb66fe,36c718d4-3ee5-42c6-a524-6c5461d61e93GroupName4
      GroupName1
      CA Policy 24d502719-f081-47c7-a263-b51a9699408e,1f452b67-77b9-43cc-aa52-82fb931b4546,36c718d4-3ee5-42c6-a524-6c5461d61e93

      GroupName3
      GroupName4

      GroupName2

      CA Policy 3554dc39f-a4a2-422d-b298-c3eb1fdb66feGroupName1



  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    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