July 11, 2016
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?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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.
July 11, 2016
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
July 11, 2016
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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.
July 11, 2016
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?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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
July 11, 2016
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
You can merge the Queries, you will have both parts and orders in a single table. From the merged query, you can create a single report, that will display parts, orders and parts per order. See this article: excel-compare-two-lists
July 11, 2016
Hi Catalin,
I am still confused for the merge query, forget to mention that the two table is not relate to each other and their id cant link together, just having the same column on this two reports, so can use the merge method?
Possible to use vlookup formula for multiple if condition?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Have you tested the sample file from the link I sent in the previous message? You have great examples on that tutorial, that can help you achieve what you want.
I think that the Merge method will work for you, it's doing the same thing as a Lookup function, it's better than using functions.
Let us know if you managed to make it work.
July 11, 2016
Hi Catalin,
Yes i have been looking for your link and testing it. Unfortunately i do not know how to apply on my case.
Should i choose inner join during merge? I want everything come out including null values.
I don't understand for the purpose after merge the second table. the header name will become eg: customername1 in query editor. And next step how i going to formula the sum with both of the tables to get the percentage i want? Where to custom the formula lookup function inside the editor?
Need your kind help again.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
You should use the Join type to “Full Outer (all from second, matching from first)”
Normally, you should be able to Join the tables by matching multiple columns, as described in the tutorial (like a vlookup with multiple criterias).
You have a Button in the Transform tab that can be used for data grouping.
Use the Group By button, and group by customer name and quarter, and any other relevant attribute, this will aggregate the data, and you will be able to add a simple calculated column to divide Net Amt USD - Acct Rate/Net Amt USD - Acct Rate.1 (the data will be in the same row, so the formula is very simple.
I think you should take the Power Query course 🙂
July 11, 2016
all from second, matching from first is Right Outer? Full Outer is all rows from both
I am sorry, still want to clarify that the merge concept is by lookup the multiple criteria from second table and the calculation is based on row divided by row? so both tables'id must have same relationship on each other? My two tables does not have any unique key that have the relationship to each other
Can you help to check which step i did wrong? I could not get the correct percentage,i am worry that i merge up the two not related tables and group by all of them and become mess up. 🙁
I merged with Full Outer(all rows from both) and group by customer,quarter,platform,country for two tables.And i sum the two columns for both tables and added a custom column for percentage,but still not getting correct result.
The power query course i am interested,maybe for future enhancement will gonna use it. But for now temporarily not yet. 🙂
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Please prepare a sample file , with source data from the same file, paste some relevant sample data into 2 excel tables than make your queries, you should know that I cannot check your steps, because I do not have the credentials required to connect to your database.
Merging by Full.Outer join type will do the trick. Selecting multiple columns as keys will finally create a unique combination, even if there is no unique ID. That's similar to a Vlookup after multiple criterias, as I said before. If you select 3 or 4 columns as keys, will aggregate data that matches all those 4 columns in the same row, there is usually no chance to have duplicates in all columns.
July 11, 2016
Oops..apologize forgot you do not have the credentials.
Attached the sample file for the two tables source,powerquery merge table and including the several settings for your reference.
The indentgeocountry1,customername1,.....is refer to parts table.
Hope the info enough for your checking, thanks again.
The file is exceed limit, so i try to include in google drive,hope you able to extract it here.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Check the attachment for an example.
The formula should check if the denominator is not zero, otherwise the result will be an error.
When you make the grouping, you have to group by the relevant fields, for example choosing to group by Platform and Platform.1 is a nonsense, as this field was used as a key for merging the tables, so it has the same value.
July 11, 2016
Catalin, i am testing your sample, kindly refer to the customer tab,the percentage is still not accurate?
I compare with the original source and the group by merge table, the amount is not tally...the matching columns to merge the table i choose is wrong or grouping wrong?
1 Guest(s)