Creating a table with multiple values filtered from same table

[TM_Evan]

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

[TM_Evan]

Desired Result

Order Number                    Owner Loan                    Product Loan

Order Number                    12345                               3456

[Anonymous]

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

[TM_Evan]

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#]))

[Patina]

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.

[TM_Evan]

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?

[nagashree]

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.

[TM_Evan]

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

Table A
RootId#	Number
-2147479388	FL-16-10-1632
Table B
RootId#	Number	Type
-2147479388	FL-16-10-1632-1	1
-2147479388	5011312-0488477e	2
-2147479388	5011412-0436634e	9

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.Number	B.OwnerPolicyNumber	B.LoanPolicyNumber
FL-16-10-1632	5011412-0436634e	5011312-0488477e

Not like this:

A.Number	B.OwnerPolicyNumber	B.LoanPolicyNumber
FL-16-10-1632
FL-16-10-1632	5011412-0436634e
FL-16-10-1632		5011312-0488477e

[dkay84_PowerBI]

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]

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.