Table 1 data into Table 2 as dimension

[DEMONIO_AZUL]

Hello there,

I have two tables, which have no relationship, but I need to add info from one table into the other as a dimension.

These are my input tables:

and

And this is the desired output table:

The input tables come from different "owners" and have more records, I just need to know how to create the [Desired Output]: at the load script.

If it were only one project, I would have added this:

'NBU' as [Project Acronym]

when loading [Systems Data]:

But since this value [Acronym] can be variable and it comes from a file, I need to read it from [Project Data]: and "append" it to [Systems Data]: somehow. However, since there is no field that can provide a link between the two input tables, no JOIN works.

Any ideas will be appreciated!

Thank you!

[Vegar]

You could try to do a full outer join.

Output:
LOAD * inline [
Name, Acronym
New building,NBU
];

JOIN (Output)
LOAD * inline [
Systems,Scope
Sys1, HVAC
Sys2, ACcess control
Sys3, Water piping
];

[diegozecchini · Accepted solution]

Hi!

then one from @Vegar is a perfect solution.

you could use also
// Load Project Data and store the Acronym into a variable
ProjectData:
LOAD
Acronym AS ProjectAcronym
FROM [ProjectData.xlsx]
(ooxml, embedded labels, table is Sheet1);

// Store the Acronym in a variable
LET vProjectAcronym = Peek('ProjectAcronym', 0, 'ProjectData');

// Load Systems Data and add Project Acronym dynamically
SystemsData:
LOAD
'$(vProjectAcronym)' AS [Project Acronym],
Systems,
Scope
FROM [SystemsData.xlsx]
(ooxml, embedded labels, table is Sheet1);

but it works because you have only one project, do you have multiple projects?

[DEMONIO_AZUL]

Thanks, Diego.

What you proposed worked perfectly - the JOIN didn't because there were no common fields and the result was a bigger table with one row empty with only Acronym = RSP.

[DEMONIO_AZUL]

Thanks, but I had already tried the full outer JOIN and it did not work. There are no common fields between the two tables and the JOIN only added a row to [Systems Data]: adding Acronym as another column but the other two fields (Systems, Scope) were empty. 

[diegozecchini]

Good! Glad it helped