Forum Discussion

TM_Evan's avatar
TM_Evan
Helper I
9 years ago

Creating a table with multiple values filtered from same table

I have a table where the Loan Number for multiple sources reside on the same table.  The field which differentiates it is the Type Column.  I already created a column for each loan number (Owner Loa...
Need Help
Show and Tell
TM_Evan's avatar
TM_Evan
Helper I
9 years ago

Hi Parita, You have been so instrumental in helping me before, I am glad you replied to me again.

 

I have attempted to do the Pivot Column again and I keep getting the error - 'Expression.Error:  We cannot apply a pivot operation over a table that contains columns with nested data.  Please remove those columns first.'

 

Problem I have is that I do not even see any nested data.

 

I also attempted the Modeling table solution to create a new table.  Tat too does not work since It appears I am not getting the syntax correct to include filtering. 

 

Any suggestions?

TM_Evan's avatar
TM_Evan
Helper I
9 years ago

Perhaps I am not being descriptive enough.  Here is what my data sets look like.  Relationships are defined with rootID#

 

Table A  
RootId#Number 
-2147479388FL-16-10-1632 
   
   
Table B  
RootId#NumberType
-2147479388FL-16-10-1632-11
-21474793885011312-0488477e2
-21474793885011412-0436634e9

 

When I add a new column to filter the number field in Table B by type I can do it using an IF statement.

Example :

          OwnerPolicyNumber = IF('pfm TitleProduct'[Type] = 9,'pfm TitleProduct'[Number])

 

When attempting to do the same type of expression using calculate I get a circular dependancy.

     Owner Number = 'B'[RootId#] && CALCULATE(Values(B'[Number]),FILTER('B','B'[Type]=9 ))

 

I am trying to get an end result like this:

A.NumberB.OwnerPolicyNumberB.LoanPolicyNumber
FL-16-10-16325011412-0436634e5011312-0488477e

 

Not like this:

 

A.NumberB.OwnerPolicyNumberB.LoanPolicyNumber
FL-16-10-1632  
FL-16-10-16325011412-0436634e 
FL-16-10-1632 5011312-0488477e
  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago

    I'm having a hard time following what you describe, but just from looking at your demo data, you are trying to pivot the Number field by RootId# so that for every RootId# you have 3 columns? 

    • TM_Evan's avatar
      TM_Evan
      Helper I
      9 years ago

      What I am attempting to do is pivot the number field with the Type so that the 2 of the 3 Number types will display on the single rootId#.

       

      I did manage to add a SQL data source using a SQL query with a degree of success.  Only thing with this was that I was only able to Import vs DIrectQuery.  So I am definitely getting closer to my solution.

       

      I really appreciate all of the feedback since it is growing my knowledge greatly.