Unanswered

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

(0) Share

Report

Posted on by Community Power Pla...

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

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Cam 185 on at

Like (0)

Report

hello @Anonymous

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

book for Barks.xlsx

Was this reply helpful? Yes No
Cam 185 on at

Like (0)

Report

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 😄

Was this reply helpful? Yes No