Forum Discussion
Helper ICreating a table with multiple values filtered from same table
Not super sure if this will work in your particular scenario, but the "Pivot Column" button under Edit Queries does exactly what ur asking.
Helper IThank 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.
- 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?
- 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
