But how can this be automated? Your connection is to one file with hard coded numbers, so for the next month you need create this again or just add the data of the new month?
Where can I learn more about PowerPivot and the KPI side, please? This is super interesting but I cannot find any useful resource beyond this fantastic video.
Why all these Power Pivot examples are using external static data tables, no one shows how to add tables (multiple) from the same excel file that is been updated constantly depending on the data entered. It is not just a dumb data base from a company wanting to know stuff. Sometimes you can create tables for project take-off that have different categories and many items that have different evaluation parameters thus making it difficult to put all on the same table, but you want to match them all in a pivot table to be able to prepare a proper proposal.
Thanks! My only concern is 99% of examples assume the ideal perfect data structure whereas it’s not the case in real life. In the ordinary course we see tables packed with nuisances and exceptions like dealing with many to many relationships. How do you create relationships efficiently when there are duplicates values in the connected data? I think that’s where the real heavy-lifting begins and if one doesn’t get past such issues it becomes a waste. In this case I believe you’d need to create bridge tables etc. But what’s the normal cadence or most efficient method to get around many-to-many relationships w/o adding too much superfluous information to your workbook?
This is the same isssue that I ve faced while working with duplicate sets containing duplicate entries. I tried creating a data model using power pivot but I kept getting errors.
When using Power Pivot I struggle when you have a table of invoices where an invoice has many line records. Can you please create if we need a junction table to match many to many. Thank you
Hi Kenji what if there is duplicate data in the file e.g when we compare budget with actuals same codes/project site will be repeated in both budget and actual file as we have to compare actual against budget.
Before any analysis, clean your data to ensure accuracy. This involves: Removing duplicates: Use Remove Duplicates in the "Data" tab. Text functions: Functions like TRIM, CLEAN, LEFT, RIGHT, and MID help clean up text data, removing extra spaces or extracting specific parts. Find and Replace: Use Ctrl + H to find specific values and replace them, especially useful when standardizing formats (like dates or currency).
Anyone have any issues with the relationship between 'Employee level' and 'Level'? Its saying it cant because one of the columns needs unique which it does.... Level obvisouly has dups but employee level is fine and ive checked the excel... Any ideas whats going wrong?
Power pivot is ultimately an analytical framework, while power query is a transformative one. Analysis can be done in power query as well, but power pivot basically streamlines your analytical process.
People are asking about duplicates in the original dataset. I think you have to do data cleaning beforehand. 1. Data Cleaning Before any analysis, clean your data to ensure accuracy. This involves: Removing duplicates: Use Remove Duplicates in the "Data" tab. Text functions: Functions like TRIM, CLEAN, LEFT, RIGHT, and MID help clean up text data, removing extra spaces or extracting specific parts. Find and Replace: Use Ctrl + H to find specific values and replace them, especially useful when standardizing formats (like dates or currency). Tomorrow I want to try Power Pivot. It's the second vid I'm watching. To create the diagram-workflow the rationale is to find similar items across different tables and create the relationship 1-many? The layout reminds me of make.
Hi Kenji! what can I do in this case: I got warning "An exception occurred: [DataSource.Error] Access to the path '/Users/tran...huyen/Documents/N01.12.2024/AventureWorks.xlsx' is denied. (Session ID: c0f2fb6e-aabf-455b-9c37-d1d3ca65f469)" when I got data Power Pivot. My computer is MacBook Tks Kenji!
🚀Get the FREE Data Analytics Guide from Hubspot: clickhubspot.com/zsm
Thank you Kenji, looking forward for the next part of it. 😊
Thanks Kenji! Need Part 2 video explaining basic and essential DAX measures. Crisp and clear explanation makes your channel unique. Congrats!🎉
Woow! this made me understand how to use powerpivot instantly, thank you very much, I am a big fan of your videos Kenji.
I like your vids that they are precise and not meandering with small talk of sort. THX
This is the best! we need part 2 Kenji!
wow. everyday i learn new skills from your video. i didnt know excel so powerful can do this stuff!!
Glad to hear that!
Thanks Kenji, Please I need a playlist for that not just part 2 :)
You are truly Awesome man. Your ability to make such difficult topic in simple word is simply awesome.
Thank you Kenji, please i need part 2
Super! Definitely need a part 2.
But how can this be automated? Your connection is to one file with hard coded numbers, so for the next month you need create this again or just add the data of the new month?
Brilliant! Just Brilliant Kenji!
Wt is the diff b/w Power query and Power pivot.
Thank you for making the video, definitely wants to see part 2!!!
Thanks Kenji, a part 2 would be great!!
Hi Kenji
I'm fan of your YT channel.... very well doing...
Thanks Kenji
Keep it up 👍❤
Love this ! and yes for part 2 :)
Where can I learn more about PowerPivot and the KPI side, please? This is super interesting but I cannot find any useful resource beyond this fantastic video.
Great video Kenji! Is Power Pivot not available for Mac? Thank you!
very good
kindly make more videos on Power Pivot
Wonderful understanding
Super stuff - Thank you
Please I can't see the power pivot when I press Add-in
Why all these Power Pivot examples are using external static data tables, no one shows how to add tables (multiple) from the same excel file that is been updated constantly depending on the data entered. It is not just a dumb data base from a company wanting to know stuff. Sometimes you can create tables for project take-off that have different categories and many items that have different evaluation parameters thus making it difficult to put all on the same table, but you want to match them all in a pivot table to be able to prepare a proper proposal.
Do you know of any videos on youtube that show it like you mentioned? Would like to see
@@potatocraftd6629 no, Im trying to work on my own. I had to watch a lot of tutorials grabbing details until Im managing to build my own
I'll share tomorrow
@@kuldippandey9672did you share it?
Thanks! My only concern is 99% of examples assume the ideal perfect data structure whereas it’s not the case in real life. In the ordinary course we see tables packed with nuisances and exceptions like dealing with many to many relationships. How do you create relationships efficiently when there are duplicates values in the connected data? I think that’s where the real heavy-lifting begins and if one doesn’t get past such issues it becomes a waste. In this case I believe you’d need to create bridge tables etc. But what’s the normal cadence or most efficient method to get around many-to-many relationships w/o adding too much superfluous information to your workbook?
This is the same isssue that I ve faced while working with duplicate sets containing duplicate entries. I tried creating a data model using power pivot but I kept getting errors.
Very helpful!
When using Power Pivot I struggle when you have a table of invoices where an invoice has many line records. Can you please create if we need a junction table to match many to many. Thank you
Thank very brother.
Your video is really helping.
Please make more video on power bi visualization.
Hello sir i am your big fan.
Part 2 please you are golden
We couldn"t see you for a long time
Hey! What do you mean couldn’t see me? 😆
Excellent video.
Nice one
Please van you make a series for Fresh new accountant that how to take start and become expert from zero level to advance level please.
i would like to know how to make a relationship between two tables many to many
Hi Kenji what if there is duplicate data in the file e.g when we compare budget with actuals same codes/project site will be repeated in both budget and actual file as we have to compare actual against budget.
Before any analysis, clean your data to ensure accuracy. This involves:
Removing duplicates: Use Remove Duplicates in the "Data" tab.
Text functions: Functions like TRIM, CLEAN, LEFT, RIGHT, and MID help clean up text data, removing extra spaces or extracting specific parts.
Find and Replace: Use Ctrl + H to find specific values and replace them, especially useful when standardizing formats (like dates or currency).
such a good guy
Hi Kenji, anyway I can get in contact with you ?
I Keep getting error when i put in related(employee[name]) any advice?
can you please tell me what version of Excel you are using?
May be Excel 365, the latest version
thanks
Interesting topic. May i know what ms excel version you used for this topic. Thank you
Pretty good, like MS Access only easier.
Thank you sir❤
I'd love to but PP doesn't work on a Mac 😕
Anyone have any issues with the relationship between 'Employee level' and 'Level'? Its saying it cant because one of the columns needs unique which it does.... Level obvisouly has dups but employee level is fine and ive checked the excel... Any ideas whats going wrong?
Got it working on the excel file you provided. I was using my own typed out version which is identical so i have no idea why it doesnt work on mine...
Thank you
is this simialr to power query?
Thinking the same. How is the power pivot different from power query?
@@sallyho146because one is called power pivot and the other is called power query😂
Power pivot is ultimately an analytical framework, while power query is a transformative one. Analysis can be done in power query as well, but power pivot basically streamlines your analytical process.
Part 2!!!
People are asking about duplicates in the original dataset. I think you have to do data cleaning beforehand. 1. Data Cleaning
Before any analysis, clean your data to ensure accuracy. This involves:
Removing duplicates: Use Remove Duplicates in the "Data" tab.
Text functions: Functions like TRIM, CLEAN, LEFT, RIGHT, and MID help clean up text data, removing extra spaces or extracting specific parts.
Find and Replace: Use Ctrl + H to find specific values and replace them, especially useful when standardizing formats (like dates or currency). Tomorrow I want to try Power Pivot. It's the second vid I'm watching. To create the diagram-workflow the rationale is to find similar items across different tables and create the relationship 1-many? The layout reminds me of make.
Part 2 please
Need part 2
part 2
👍
🙏🏻 you missed me..? 😅
Hahah always!!
Hi Kenji! what can I do in this case:
I got warning "An exception occurred: [DataSource.Error] Access to the path '/Users/tran...huyen/Documents/N01.12.2024/AventureWorks.xlsx' is denied. (Session ID: c0f2fb6e-aabf-455b-9c37-d1d3ca65f469)" when I got data Power Pivot.
My computer is MacBook
Tks Kenji!
Thank you