Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TM_Evan
Helper I
Helper I

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 Loan and Product Loan).

Issue I have is that when I attempt to addit to a table they insist on creating seperate rows.  I would like for it to be on one row with a single order number which it applies to.

 

Any suggestions?

Example.

Table A

 

Number                    Type

Order Number            1

12345                          2

3456                            9

9 REPLIES 9
TM_Evan
Helper I
Helper I

Desired Result

 

Order Number                    Owner Loan                    Product Loan

Order Number                    12345                               3456

Anonymous
Not applicable

Not super sure if this will work in your particular scenario, but the "Pivot Column" button under Edit Queries does exactly what ur asking.

Thank you Scottsen for replying to this post.  I am still relatively new to Power BI and the suggestion you made is something I have never even considered.  I did try it today with no success unfortunately with error messages stating my data is nested. 

 

The solution I am going for is more along the lines of DAX expressions.  I am also realizing that I will need to do calculated columns in a table where I only have 1 unique root id.  I unfortunately keep getting errors - ' A table of multiple values was supplied where a single value was expected.'

 

Funny thing is if I look at the raw data of table B (Where the loan data is, for the one unique root ID we have 3 rows for each of the 3 loan numbers suppled.  My 1 column is only providing the logic to return 1 of them.

 

Here is the DAX statement used.

OwnerLoan = CALCULATE(VALUES('pfm B'[Number]),Filter('B','B'[Type] = 2 && 'A'[RootId#] = 'B'[RootId#]))

Yes Evan the Pivot Column option would serve you well if you were wanting to create the columns within your original table. You would use Type as your pivot column and Number as Values.

 

If you want the information in an additional table, that expression should work.

 

You might also try 'summarizing' your table under the Modeling tab. You would create a New Table and the expression would be  = SUMMARIZE('Name of your Table', 'Name of your Table'[RootID#]). Then create the 3 columns bringing in the Number by filtering by Type and RootID.

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?

Do you have any references to other tables in any of your columns. If so, please try removing the Foriegn key reference column and try pivoting.

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

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? 

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.