Table Import and Power Query Editor

[michaelhilland]

Hi there, 

I have imported three tables of data:

Table One

Vendor	Product Code	Type	Year	Quarter	Units	Plastic Weight	Steel Weight	Inserted column via power bi new colum	Inserted column via power bi new colum
Braiform	Size Cubes	Virgin	2020	1	22,777,358	0.5	0
Braiform	Plaques	Virgin	2020	1	6,639,923	1.8	0

Table One

Vendor	Product Code	Type	Year	Quarter	Units	Plastic Weight	Steel Weight	Inserted column via power bi new colum	Inserted column via power bi new colum
Braiform	Size Cubes	Virgin	2020	1	22,777,358	0.5	0
Braiform	Plaques	Virgin	2020	1	6,639,923	1.8	0

I have inserted two new columns in the above tables and added a formula to look up to the third table and calculate a value which is successful. 

Third imported table:

Country	Year	Quarter	CH Sales	Percentage Split
UK Stores	2020	1		73.69%
UK e-Comm	2020	1		6.18%

However, I am now wanting to merge the first two tables to get a "full-year" table. When merging, only the columns from the original import merge together and the columns that I have added to not appear. Is there a way around this?

[michaelhilland]

Morning, 

Thanks for the response. My organisation prevents from using links to allow me to share the pbix. I will take some screenshots below:

Q1

Q2

The last three columns in the above tables are a calculation I completed in powerbi. 

I then go to the edit queries and the Power Query Editor opens. 

The final three columns in the above table do not appear when I open them in the Power Query Editor:

Is there a reason why I cannot see the three columns that I inserted? 

These columns have a calculation which looks up another table:

UK Stores Units = LOOKUPVALUE('Q1 Location Split'[Percentage Split],'Q3 Location Split'[Country], "UK Stores")*Q1[Units])

 

Essentially, my end goal was to merge these tables together to eventually create a year-end table (four quarters)

[HotChilli]

The new columns have been added with DAX in Powerbi front end.  Power Query doesn't know anything about them.

Options:

Add the New columns in Power Query and Append the tables OR

Append the tables in Power Query and add the columns (using DAX) to the final table

[michaelhilland]

Thanks for the quick response. 

First Option

I tried adding the new columns in Power Query but it doesn't allow me to do the following calculation as it doesn't allow to look up a different table:

UK Stores Units = LOOKUPVALUE('Q1 Location Split'[Percentage Split],'Q3 Location Split'[Country], "UK Stores")*Q1[Units])

Is there a way to look up a different table?

[HotChilli · Accepted solution]

Power Query doesn't use DAX language, it uses M.

Yes, there are ways to look up other tables via Table Merge or M but if this is all new to you why don't you append the tables first and add calculated columns in DAX after.  (If this makes sense in your data model)

[HotChilli]

If you link the pbix here, I'm sure this can be sorted quite quickly.

It does suggest that the 'Append' query is referring to the tables in an earlier form

[parry2k]

@michaelhilland sorry your question is not very clear, can you explain a bit more and also first two tables are identical?

[michaelhilland]

Hi, 

Thanks for the response. 

The first two tables will have different data sets but the columns are the same. 

I have imported two separate tables as above. Table one and Table Two. 

I have inserted two new columns using the insert column option in powerbi. 

I now want to merge these two tables into one. When I do this using the append and adding the two tables. The new columns I have added to not appear, only the columns of the original dataset that I originally uploaded.