MS Power Query- Importing Data

I'm trying to do something and not sure if I am even going about this the correct way.  I'm trying to import data (a few columns) from someone else's excel file.  I am able to import the selected columns into my excel file without any issues.  I can update and see the new data from the owner's file.

So here's my problem:

My file, I have added some additional columns with data.  When I import (or refresh the data) from owner's file into my file, the imported columns and do not match the added columns of my file.

Below is an example of the owners file.

Row ID title Qty OK?
1 Apple 2 Yes
2 Orange 10 No
3 Pineapple 5 Yes

Below is an example of my file with the my added columns, to the far right.

Row ID title
Qty OK? Shipped Complete
1 Apple 2 Yes Yes Yes
2 Orange 10 No No Yes
3 Pineapple 5 Yes No No

When the owner adds a new row of data in between rows 2 and 3, I then refresh my table (query), row 3 gets bumped down and the added or new data shows up in the correct location, however, the data that is in my columns on the far right does not get bumped down. along with the original data.  The owners file has about 20 plus columns of data.  I only want a couple of columns.  I need his columns because his is the master tracking worksheet.  I take his columns and add my columns to the far right and add supporting data to each row.  Each row will have different data.  When i repeat the process and update my table with his updated information, then my added columns do not move or index with the original data.

The owners excel file is just a excel file.  My file, after I import the data becomes a table.  I don't think this has any impact on my problem.  

I am unable to provide the actual files due to the type of information.  I've tried to create these to two sample tables above.  The owner adds data in between rows of existing data because the type of data and how it's arranged or grouped by family.

Thank you,

Pat

|

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Hi PatrickSnyder,

I am John, an Independent Advisor and a Microsoft user like you. I'm here to work with you on this issue.

The owner of the file should also have the same column you've just added, so every time you would update your Excel file using the owner's file, the columns would be automatically updated too.

I hope it helps you to sort it out. If not, please reply back to this forum to let me know. I will be glad to follow up and help you.

Regards,
John

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

John,

I would agree with you however my needs or added columns are different from the owner, so the owner is not going to add the columns that I need.  

Any other options?

pat

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi PatrickSnyder,

Let's check with other users if they have any other ideas. They would be notified and would reply to this thread when they are available.

Hope you would be able to find the solution to this issue.

Thank you and stay safe!
John

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

You should be able to use PowerQuery to delete unwanted columns AND to add new, blank columns.

.

It takes an extra step. You have to create a separate table with the new column(s) in it and a "key" column you can use to join the data in the 2 tables, ie the "RowID" column.

.

Merge the queries on the index column

Expand the column, note the descriptive Query name

Only keep the new column you want to add

Now you can right click on the Power Query ouptut (green) table select refresh to import new data from outside file

Add new data to the orange table, refresh to show the new data

Add new rows to orange new col table, refresh query output table to import that data also

Note: before creating the Query, change the table names to make them unique and descriptive. This table name will be applied to the new query, making it easier to relate them together.

You can look at this example in my online example file
https://1drv.ms/x/s!Am8lVyUzjKfpowARghSdfg1koqJf?e=H0lKb5
Link to this workbook. Look for the Tab with your ID on it

.
*****
.
As computer scientists we are trained to communicate with the dumbest things in the world – computers –
so you’d think we’d be able to communicate quite well with people.
Prof. Doug Fisher

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Rohn007, thanks for your help.  I was looking at doing just that but got writers blocks and seemed like a lot of work.  Maybe it's a little easier now since I see it.  Give me a day or two and I'll tell you if it works.

Thanks,

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Funny thing, I just found this article now with a similar technique, except he uses a full copy of the original table. It eliminates the need for the separate "comments" table

Self Referencing Tables in Power Query To Add data/comments          2016 06 21      Matt Allington
https://exceleratorbi.com.au/self-referencing-tables-power-query/
I have had this idea in my head for over a year, and today was the day that I tested a few scenarios until I got a working solution. Let me start with a problem description and then my solution.
.  *  Add Comments to a Bank Statement
.  *  Enter Self Referencing Tables
.  *  Incrementally Add Comments
.  *  What if I don’t have a unique ID column

.
*****
.
As computer scientists we are trained to communicate with the dumbest things in the world – computers –
so you’d think we’d be able to communicate quite well with people.
Prof. Doug Fisher

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I got to that article from this article:

Fixing Incorrect Data in Power BI                  2020 08 08   Matt Allington
One of the features of Power BI (and Power Pivot) is that data cannot be changed once it is loaded into the database – this is by design. Power BI is a reporting database, it is not a spreadsheet.  Power Pivot and Power BI are built to faithfully report on the data that is loaded and do not allow a user to “change” the data after load. This paradigm is very different for those that live in an Excel world. When you use Excel, you can override any single number (or numbers) you want to, in case you need to make such a change.
.  *  The Best Solution – Fix the Source!
.  *  Self Referencing Tables
.  *  Using Override Tables
.  *  Replacing Incorrect Records With An Override Table
.

.
*****
.
As computer scientists we are trained to communicate with the dumbest things in the world – computers –
so you’d think we’d be able to communicate quite well with people.
Prof. Doug Fisher

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Rohn007,

Testing out what you have provided and what if my source file does not have a RowID?

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Rohn,

I'm trying your very first steps and it's not working.  I started fresh.  I imported my source data.  I created a second tab and this is where you instructions create questions.  For my second tab, is it a copy of my first tab with the new columns and the columns that i don't want, deleted?  Do Is my new second tab just a new table?  If so, how do i get the correct number of rows/rowIDs?

When I created a second tab (copied from the first tab, with new data and duplicated columns deleted from the first tab), I create a query to import the new tab/table into the worksheet.  Now I have three total tabs.  tab1 = imported source data from owner.  tab2 = new table with added data.  tab3 = new table created from query of tab2.

I go to merge and after selecting the two tables, the "ok" button is grayed out and i don't get the check box below or the additional comments about fuzzy logic.

Where am I going wrong?

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Rohn, here's a snippet of the merge screen with the "ok" button grayed out.

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated January 28, 2021 Views 136 Applies to: