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

[Barks123]

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 = 

BLCode	ClaimMonth	ClaimYear	Category
B344	March	2022	UC

tbl_data contains main data = 

ID	Surname	Forename
1	Blah	Test
2	Foo	Blaaah

I want = 

ID	Surname	Forename	BLCode	ClaimMonth	ClaimYear	Category
1	Blah	Test	B344	March	2022	UC
2	Foo	Blaaah	B344	March	2022	UC

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

Thanks

[Cam]

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)

Get Headers function - returns the Extract Headers query table

Extract Headers query - extracts your header data

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.

i hope this helps.
workbook attached

[Cam]

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 😄