Forum Discussion
Table Import and Power Query Editor
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?
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)
7 Replies
- parry2k
Super User
Anonymous sorry your question is not very clear, can you explain a bit more and also first two tables are identical?
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.
- HotChilli
Community Champion
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
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
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?
