column type Table is list of guid

[Anonymous]

Hi all,

My data source is MongoDB, the column is of a type 'list of GUID' but Power BI is reading as a type Table as shown below.
I want the values of IncidentType, but when I try to expand, the number of rows will increase as for every unique id, there could be more than one IncidentType.( refer to picture 2). I have more than 40 such columns (of type  'list of GUID' ) that I need to expand and I know it's not feasible to do so. Is there any other way where I can get the IncidentType value without expanding?

Note: Once after expanding, I need to create a relationship with MasterTable to get the real values.

2. 

 

Regards,

Ashfiya

@amitchandak 

[PhilipTreacy]

Hi @Anonymous 

You haven't explained what your desired result is or provided any example of the same.  Hard to know how to help you if I'm not sure what it is you want.

You have possibly 40 columns? 

All of the data in these columns is in tables?

You want some of the data in these tables extracted?  Are they all GUID's?

Do you want all the extracted data in a single column?

What do you want to do with the other data in the original table like the Primary key?

Regards

Phil

[Anonymous]

Hi,

You have possibly 40 columns? 

Yes can be more than that

All of the data in these columns is in tables?

Yes all are in table format

You want some of the data in these tables extracted?  Are they all GUID's?

yes, yes all are guid's

Do you want all the extracted data in a single column?

Yes, Expanding will give me in the same column itself so thats not the issue

What do you want to do with the other data in the original table like the Primary key?

It will be repeated and increases the rows.

Consider the below screenshots.

1. Before expanding the IncidentManagemnets_IncidentType column there are 689 rows as of now.(image 1 )

For RecordNo INC_01-2022-2 ( 1st row of the table ) contains threeIncidentType and INC_02-2022-3 (2nd row) contains four IncidentType. After expanding every unique id will get repeated. ( refer image 2 )
The other guid columns will contain diffirent set of values. ( refer image 3 )

1.Image 1

2. Image 2

consider exaple of two list of guid columns 

1. IncidentManagements_IncidentType
2. IncidentManagements_SubstantialActs

The RecordNo contains INC_01-2022-2 ( 1st row of the table ) contains three IncidentType and two SubstantialActs.(image 3 and 4 ) If i expand 'IncidentType' INC_01-2022-2 will get repeated 3 times and expanding SubstatialActs i.e
          3           *             2             =  6 repetitions of record No INC 01-2022-2 (image 5)

(incidentType)  (SubstantialActs)

Similarly i have such more 40 number of columns which contains different set of values. Expanding all 40 columns will repeat the unique id resulting in more number of rows and performace issue. 
So is there any other possible way? 

I know of a solution (similar scenario) where we can create custom column and extract values by comma or so.

Creating 40 custom columns is not a feasible way and extracting values by delimiter will also not work why because I would require those guid (value) row by row to create relationship to Master Table.

3. Image 3

4. Image 4

5.  Image 5

[v-easonf-msft]

Hi, @Anonymous 

Please check if the solution mentioned in the thread below works.

https://community.powerbi.com/t5/Desktop/Expand-multiple-columns-in-power-query/m-p/695129 

Best Regards,
Community Support Team _ Eason

[PhilipTreacy]

Hi @Anonymous 

It's not entirely clear what you want as a final result.  Do you want to keep the Primary Keys (and hene link them to the Incidence Types)?

Do you need to extract the Incidence Types from 40 columns?

Do you want a single column with all the Incidence Types together or 40 columns?

Please provide an example of your desired result.

You can extact a column from a table, so in yor instance you can add a Custom Column with this code

=[IncidentManagements_IncidentType][IncidentType]

Which will give you a column containing a list on each row.  As I said I'm not clear on your final goal so not sure what you need to do from here.

Please provide more information.

Regards

Phil

[Anonymous]

Hi @PhilipTreacy ,
Thanks for the reply. Creating a custom column will work. But I have to expand the values to rows. And this is just for 1 column called IncidentType. There could be 40 more different columns where I'll have to create 40 custom columns and need to expand which will lower the performance right?