, Merge two rows

[PowerBItest007]

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

[MarcelBeug · Accepted 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"

[ibarrau · Accepted solution]

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,

[ibarrau · Accepted solution]

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,

[PowerBItest007]

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

[ibarrau]

Did you try what MarcelBeug said? because my suggest is a matrix if you want all information in only one visualization. 

Regards,

[PowerBItest007]

it shows me 
Expression.Error: The name 'Adress' wasn't recognized.  Make sure it's spelled correctly.

[PowerBItest007]

it is worrrrkkk thanks 

but can you plz explan to me the DAX i am new in Power BI

[ibarrau · Accepted solution]

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,

[MarcelBeug · Accepted 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"