cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBItest007 Frequent Visitor
Frequent Visitor

, Merge two rows

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: , Merge two rows

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"

 

Specializing in Power Query Formula Language (M)
ibarrau Established Member
Established Member

Re: , Merge two rows

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 Established Member
Established Member

Re: , Merge two rows

Nice, I am happy you made it! Smiley Happy

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,

7 REPLIES 7
Super User
Super User

Re: , Merge two rows

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"

 

Specializing in Power Query Formula Language (M)
ibarrau Established Member
Established Member

Re: , Merge two rows

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 Frequent Visitor
Frequent Visitor

Re: , Merge two rows

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 Established Member
Established Member

Re: , Merge two rows

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

 

Regards,

PowerBItest007 Frequent Visitor
Frequent Visitor

Re: , Merge two rows

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

Highlighted
PowerBItest007 Frequent Visitor
Frequent Visitor

Re: , Merge two rows

it is worrrrkkk thanks Smiley LOL

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

 

ibarrau Established Member
Established Member

Re: , Merge two rows

Nice, I am happy you made it! Smiley Happy

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,