Forum Discussion
Creating a table listing of records where a client code appears in the Title text string
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", "")
Select By Delimiter > Custom > Comma. Expand the Advanced Options button and select Rows. Click on OK.
5 Replies
- Ashish_Mathur
Super User
Hi,
In the Records Table, use the Query Editor to split "Description of Contents" column by rows.
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
Select By Delimiter > Custom > Comma. Expand the Advanced Options button and select Rows. Click on OK.
