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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
daicaboy
Frequent Visitor

Combine data from 1 row to all row

Hello,

I have 2 column from 2 file and i want to combine data from 1 row in column 1 to all row of column 2.

Data 1                               

Column 1
A
B
C

 Data 2

Column 2
X
Y
Z

Result

Result
AX
AY
AZ
BX
BY
BZ
CX
CY
CZ

Thank you for your time!

2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
Super User

=List.TransformMany(Data1[Column1],each Data2[Column2],(x,y)=>x&y)

View solution in original post

v-jingzhan-msft
Community Support
Community Support

Hi @daicaboy 

 

@wdx223_Daniel 's solution is great with only one step. It can give you the desired result in a list. 

 

If you have interest in dealing with it with Power Query Editor GUI, you can try the following steps.

1. In "Data 2" query, add a custom column with #"Data 1"[Column 1]. This gives you a list of data from Data 1 query. 

vjingzhanmsft_0-1714011986509.png

2. Expand the above custom column to new rows. 

vjingzhanmsft_1-1714012002719.png

3. Add the second custom column to combine values of two columns with &. 

vjingzhanmsft_2-1714012444068.png

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

View solution in original post

3 REPLIES 3
daicaboy
Frequent Visitor

Wow, thank you guys, it help me a lot. I appreciate it !

v-jingzhan-msft
Community Support
Community Support

Hi @daicaboy 

 

@wdx223_Daniel 's solution is great with only one step. It can give you the desired result in a list. 

 

If you have interest in dealing with it with Power Query Editor GUI, you can try the following steps.

1. In "Data 2" query, add a custom column with #"Data 1"[Column 1]. This gives you a list of data from Data 1 query. 

vjingzhanmsft_0-1714011986509.png

2. Expand the above custom column to new rows. 

vjingzhanmsft_1-1714012002719.png

3. Add the second custom column to combine values of two columns with &. 

vjingzhanmsft_2-1714012444068.png

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

wdx223_Daniel
Super User
Super User

=List.TransformMany(Data1[Column1],each Data2[Column2],(x,y)=>x&y)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors