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

Dataverse import excel, power query - add column from different table and repeat in all rows of another table (tables are not related)

I need to import an Excel spreadsheet. It has a main data range tbl_data and a separate header range tbl_header. I want to use dataflow to import tbl_data but insert 5 columns that repeat values from the first (and only) row of data in tbl_header.

 

e.g. tbl_header = 

 

BLCodeClaimMonthClaimYearCategory
B344March2022UC

 

tbl_data contains main data = 

 

IDSurnameForename 
1BlahTest 
2FooBlaaah 

 

I want = 

IDSurnameForenameBLCodeClaimMonthClaimYearCategory
1BlahTestB344March2022UC
2FooBlaaahB344March2022UC

 

How easy is this? Would've taken me seconds in SQL 😞

 

Thanks

2 REPLIES 2
Cam
Kudo Commander
Kudo Commander

hello @Barks123 

 

You can do this with a Function.

beware - you need to separate your Extract / Load queries, so that you don't trigger the formula firewall.

 

Here's my example in Excel using Power Query (Dataflows method will work in the same or a very similar way)

 

Cam_0-1673326689813.png


Get Headers function - returns the Extract Headers query table

Cam_2-1673326808890.png


Extract Headers query - extracts your header data

Cam_3-1673326858655.png

 

Extract Data query - extracts your data data

 

Load Header query - references the extract header query

 

Load Data query - references the extract data query


Transform Data query - references the load data query, and calls the get headers function.

Cam_1-1673326742622.png


i hope this helps.
workbook attached

Cam
Kudo Commander
Kudo Commander

ohh. it turns out there is an even easier way..
you do a cross join
https://learn.microsoft.com/en-us/power-query/cross-join

 

you just add a custom column to your data table, and reference the header table in that custom column... done!

 

cant believe this is all that is required...wow Power Query is awesome 😄

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (3,065)