Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PowerBItest007
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
MarcelBeug
Community Champion
Community Champion

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)

View solution in original post

ibarrau
Super User
Super User

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

View solution in original post

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

View solution in original post

7 REPLIES 7
ibarrau
Super User
Super User

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

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

it is worrrrkkk thanks Smiley LOL

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

MarcelBeug
Community Champion
Community Champion

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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.