Hi Hooi,

You have to provide more details. In the file you mentioned, there is no code for connecting to the database, I hoped to find there the information I need to understand the situation.

Is it a local database, on your computer? If so, you need to type localhost as the server name, in the next field type the database name.

In the next window, click on Database (not Windows authentication), and type your access credentials, if it's a protected database. You will then be able to select the tables you want from the database.

If it's not a local server, just type the IP of that server as the server name, with the database name.

Make sure that your IP is whitelisted on that computer, but I don't think you have a problem with that, as you mentioned that you are able to connect and extract data from that server.

Hi Catalin,

Its not local database,i am connecting to our company's live database.

Oh..IP an alternative way, why can't i think that 😀 can connect now finally thank you..i can connect to the power query now.

So i should just create the connection from the power query only right? And i will make the power query workbook as the data source.

Now come back the same issue for the sum of pivot table is not working. The data type of the total cost column on databse consist of integer with comma, currently is saved as varchar so it can't be sum up. Can you help for this?

Thanks & Regards,

Hooi Thin

Yes, from Power Query tab, choose New Query-From Database-MySQL Database, type the IP and database name, then the credentials in the next window.

After selecting the table you want, choose Close and Load To, then in that window select Connection Only. (this will not load the data into worksheet).

When you create the pivot tables, instead of selecting a range, click the button:  From Connection, and you should be able to find the new query listed there.

Use that connection to create all the reports you need.

Where can I see the data errors you mentioned? Can't work with data from my memory 🙂 , you have to upload sample files.

Power Query has very powerful tools to cleanup and reformat the data imported from any source, use them to reformat the data.

Yes. I can set up the connection on power query now 🙂

Attached sample file for your reference. Its great if can reformat the data from text to integer on power query without do anything on the database.

Catalin can you guide me? 🙂

You have to go to Power Query tab in Ribbon, click on Show Queries to view the Queries Pane, right click the query and select Edit (or just double click the query). This will open the Power Query window, with its own ribbon and tools. To reformat a column, right click that column , select ChangeType, you will have many options there.

Explore the menu, there are many useful transformations that can be done, including adding columns with specific calculations, replacing values, and so on.

See these tutorials for more informations: power+query+site:www.myonlinetraininghub.com

If the file is going to be used by other users, they need to have power query installed to refresh the data. ON the first use of the file, they should open the query and enter their credentials to access the database, data cannot be seen without that. The credentials are not stored in the file, they are stored in user's computer.

You can also join the Power Query course, this will be the standard power tools for data import and processing in excel.

Yes i able to change the column to integer now. But i have values with bracket (eg : (234)) , this one can be remove with power query too?

One of the thing i noticed the power query does not compact the size of the data even power query here just act as a connection?

The features on power query is same with power pivot?

If i have 3 pivot table with different connections of power query on the same page, how am i going to refresh them ?I do this way because previously i have know that slicer not able to work together with multiple different source. Not sure Power Query can do anything for them? The data do not have unique key can mix all the 3 datasource together.

Oh that mean you can not see my table data at all? If do not have power query and do not enter the credentials to the database.

I don't want them to install power query and do those refresh thing, because i myself will refresh the data every month and send to the user.For this case, its won't have any issue for them to view the data?

Yes, you can replace characters, just like I said in previous message, there is a Replace Values button in Transform tab, in the Power Query window.

Power Query is not Power Pivot, and Power Pivot is not Power Query 🙂 , they are different tools.

Data is loaded in excel, no matter if it's only a connection, otherwise you will not be able to use the pivot table without access to the data source.

Users without power query will be able to see the existing data and reports, but they will not be able to refresh it.

If you have different queries to different source tables from database, then you will not be able to connect the same slicers to all reports, they are completely different sources. Until now, you mentioned only one external source, now there are 3 ? If the data tables can be connected with a common column (key), you can set the relationships in the Data Model, with Power Pivot, then make a report with fields from any table.

Yes. I do not want them to refresh it. 🙂

Yes, sorry, i have 3 external source total. Because the rest two data source is having all same issue, so if solved one then solved the rest.

I knew that slicer not able to connect with 3 different source, so i just give up on using slicer for that, and just putting 3 pivot table with filtered from 3 connections .Not a good design i know but no other choice as i guess.So for this case, in future how am i going to refresh the 3 connection at the same time on the same page?

Just click refresh all, will refresh everything?

They do not have common key, all the 3 reports is stand alone reports. They not link to each other. 

Yes, a simple Refresh All from Data tab will update all reports.

Thank you Catalin. I will let you know if everything success after i complete.Thanks very much! 🙂

Hi Catalin,

I have issue on scrolling the data on power query editor,i wanted to check how many of the total rows on that page, but i have difficulty on scrolling the page to the end, the bottom bar shows 999+ rows. Now i need to scroll many times only it can move a little bit, i can not find anything like go to which line?

I wanted to check this because i refreshed my pivot table which connected to power query, the rows is not tally with my original source, missing many rows.

I went to data->i clicked refresh all connections as well. I do not want to delete and create a new pivot table every month to get the updated data.Now Power query's amount column is higher than my pivot table values.

What is the correct step to make sure pivot table is exactly tally with original source on database? I feel like abit confuse for the refreshing features.

I need to refresh power query every time after i upload data to database?

Please help. 

Yes, you have to refresh all when new data is added to the source database.

The preview of data from power query window is not for analyzing each row, it's not an efficient way to do that. The source can have many million records, you cannot simply scroll them all. You can filter columns to analyze smaller subsets of your data, to detect which is the source of the problem (remember to delete the filter step after you finish evaluating the data.)

Most probably, your pivot table does not display all the columns from the source, this means that the data is aggregated into existing categories, so it will have less rows and higher values. But the total should always match.

thanks Catalin for the info. I know my issue now, i put wrong filtered location on pivot tables. 😛

Now i found some of my pivot tables appeared that error "reference is not valid" when i click refresh on it. After i have reload data to database.And then i have to recreate the pivot table with correct connection again.

Some are okay.Why it is so?

I have a pivot chart control by slicer, there is top 10 values filtered, after play around with the slicer, the top 10 does not work anymore, it will show out everything and very messy. How to fix it always showing top 10?

It's difficult to guess what is in your file, if you don't give details I cannot understand what's going on. That's why a sample file is more valuable than a thousand words. Are all your pivot tables connected to the power query we discussed? Do you still have pivot tables connected directly to the source? If so, is the source on a network drive? Is the network drive accessible when you refresh the pivot table? (you may be connected to another network...)

Remember, you're the only one that has all these informations in your mind, all the details you can give are important, don't assume that we know your environement. Screenshots of the error message may also reveal other informations.

Please provide all details, we will gladly help you.

Regards,

Catalin

I am sorry Catalin for assuming you know my situation..

I am using the power query that you suggested, and create only connection without loading data. The source connected is on production server, i am using ip address connecting to the database. The network is always accessible all the time.

Now the error "reference is not valid" gone after i recreate a new pivot table to connect. But not sure will be happen again on next time. Ignore this first.

The top 10 issue can ignore also as i do not need to filter that already.

My new question now is i do not know how to count the formula on pivot table divided by another pivot table using vlookup. (Parts/Order)

Kindly refer to the tab of Customer,i wanted to count percentage by using Order pivot divided by Parts pivot, i know to divide if the two pivot table is fixed column, but now the pivot columns might change according to data,so i am not able to use the formula to fix the column to divide the value.

I am not sure which formula is suitable to solve it? Appreciate your help again!Thanks.