Creating a table listing of records where a client code appears in the Title text string

[AF_Archives]

Hi all,

I am new to Power BI and was wondering if I could get some help with the logic and DAX of the following:

I have two tables:

1. Clients - listing Client Codes & Client Name

2. Records - listing Record# & Description of Contents 

Client Table

    

Records Table

The 'Records[Description of Contents]' field contains multiple Client codes that each record is related to.

I am wanting to build a third table that essentially holds the relationship of client codes to record#s (see below), however I am struggling to determine an efficient method to do this (This is just an example and the #client codes and record#s are much larger than shown here).

I started to create a new column (in Records table) for each client code and using the following DAX (see below) to populate each column. My thinking was that I could duplicate the table, delete the Description field, then unpivot the columns to arrive at the above desired "third table".

    SLQ Record = IF(CONTAINSSTRING('Records'[Description of Contents], "SLQ"), "SLQ", "")

    CAA Record = IF(CONTAINSSTRING('Records'[Description of Contents], "CAA"), "CAA", "")

This seems to me to be a very labourious task and not scalable, so I was hoping to determine if this whole process can be completed through DAX, maybe using variables, to perform an iterative process for each of the client codes listed in the Clients table but I just can't seem to get my head around how this would work.

I have been working on this for a couple of days and going around in circles - the other trouble is I'm not sure how to even describe what I am trying to do to find correct DAX examples. I would really appreciate any guidance and/or DAX that I can get. 

Thank you!! 😀

[Ashish_Mathur]

Hi,

In the Records Table, use the Query Editor to split "Description of Contents" column by rows.

[AF_Archives]

Hi Ashish,

I'm sorry, but I don't entirely understand. In Query Editor under the Transform tab and the Text Column group, I have the following options to "Split Column":

By Delimiter

By Number of Characters

By Positions

By Lowercase to Uppercase

By Uppercase to Lowercase

By Digit to Non-Digit

By Non-Digit to Digit

Which one of the above is split column by rows?

I may not have explained myself properly....

The actual "Description of Contents" field varies in length and format - all I can notice is that one or more Client codes appear within this field in no consistent manner.

Is it possible to use the Client table as a variable/parameter (not sure which) to search in the "Description of Contents" field for that client code and then record (somewhere) the corresponding record number that it appears within. Sorry if I didn't make that clearer before.

The actual file I need to apply this to has thousands of rows to search through and there are multiple client codes to search for. I just made up a simple example with a few dummy client codes and record numbers.

I hope that makes it a little clearer of what I am trying to do.

Appreciate your help!

[Ashish_Mathur · Accepted solution]

Select By Delimiter > Custom > Comma.  Expand the Advanced Options button and select Rows.  Click on OK.

[AF_Archives]

Excellent - Thankyou! That worked well - much appreciated 😀

[Ashish_Mathur]

You are welcome.