Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hello every one
i have qustion and wait for answar
if i have like these two tables
Name Salary
------ -------
A 500
B 200
C 300
and
Name Address
------- ----------
A USA
B CA
A GE
C KL
C BE
and there is a relation between these two table with NameS columns
the Qus is
HOW CAN I GET IN VIZUALITION table Like this
Name Salary Address
------- ------ ----------
A 500 USA , GE
B 200 CA
C 300 KL, BE
Solved! Go to Solution.
In the Query Editor you can merge the 2 tables (as a new query), add a new column with the address codes combined and then remove the column with the nested tables.
let Source = Table.NestedJoin(SalaryTable,{"Name"},AdressTable,{"Name"},"NewColumn",JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(Source, "Address", each Text.Combine([NewColumn][Address],", ")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"NewColumn"}) in #"Removed Columns"
Ok, let's talk about this.
1- Normally you will add the tables and go to RelationShip View draging with mouse one column to the other one in the other table to create a relation. Then you can add any visualization (test with table), where you add those three columns and make what you want. The problem is that you have two address in same column. You can separate in two visualizations or a matrix with address and names as rows and columns (or columns and rows) and average of salary.
2- Like he said, the formula is another way, but i think it would be more representative in separated graphics.
Regards,
Happy to help!
Nice, I am happy you made it! 🙂
We didn't talk about DAX at all haha. You can model your data in two languages in different steps.
1- The example in the previous posts is the language "Power Query" know as "M". You can access this operations in "Edit Query".
2- DAX language allow you to add columns or measures once the tabular model is created. In visual or data view, you can look for the add colum button or right click on Tables. This language is pretty similar to Excel Formulas.
Regards,
Happy to help!
Ok, let's talk about this.
1- Normally you will add the tables and go to RelationShip View draging with mouse one column to the other one in the other table to create a relation. Then you can add any visualization (test with table), where you add those three columns and make what you want. The problem is that you have two address in same column. You can separate in two visualizations or a matrix with address and names as rows and columns (or columns and rows) and average of salary.
2- Like he said, the formula is another way, but i think it would be more representative in separated graphics.
Regards,
Happy to help!
i try but it do not gave me
like this
Name Salary Address
------- ------ ----------
A 500 USA , GE
B 200 CA
C 300 KL, BE
it gave me
Name Salary Address
------- ------ ----------
A 500 USA
A 500 GE
B 200 CA
C 300 KL
C 300 BE
but this is not usefull
Did you try what MarcelBeug said? because my suggest is a matrix if you want all information in only one visualization.
Regards,
Happy to help!
it shows me
Expression.Error: The name 'Adress' wasn't recognized. Make sure it's spelled correctly.
it is worrrrkkk thanks
but can you plz explan to me the DAX i am new in Power BI
Nice, I am happy you made it! 🙂
We didn't talk about DAX at all haha. You can model your data in two languages in different steps.
1- The example in the previous posts is the language "Power Query" know as "M". You can access this operations in "Edit Query".
2- DAX language allow you to add columns or measures once the tabular model is created. In visual or data view, you can look for the add colum button or right click on Tables. This language is pretty similar to Excel Formulas.
Regards,
Happy to help!
In the Query Editor you can merge the 2 tables (as a new query), add a new column with the address codes combined and then remove the column with the nested tables.
let Source = Table.NestedJoin(SalaryTable,{"Name"},AdressTable,{"Name"},"NewColumn",JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(Source, "Address", each Text.Combine([NewColumn][Address],", ")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"NewColumn"}) in #"Removed Columns"
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |