Highline Excel 2016 Class 20: Power Query Import Multiple Excel Files & PivotTable Show Values As
HTML-код
- Опубликовано: 15 сен 2024
- Download Files: people.highlin...
In this video learn about:
1. (00:15) Overview: Clean & Transform & Import Multiple Excel Files with 1 Sheet Each & Create PivotTable Report
2. (03:00) Step 1: Import Multiple Excel Files, 1 Sheet Each, including Power Query (M Language) Excel.Workbook function.
3. (11:45) Step 2: Load to Data Model and create DAX Measure for Total Sales, Edit Power Query
4. (15:19) Step 3: Create PivotTable based on Data Model and Group Dates by Month and Year.
5. (16:50) Step 5: Use Show Values As feature in a PivotTable: % of Grand Total, Difference From, % Difference From, Running Total, and % Running Total.
6. (19:48) Step 4: Add Slicers for City and Year
7. (20:40) Step 6: Finalize Report
8. (21:40) Step 7: We Get New Files in Folder, After we Refresh: Everything Updates!
Recommended the channel to my friend, and he was amazed by the quality and helpful content of the channel.
Thanks for sharing the word that excelisfun is free and comprehensive : )
Another great video - thank you, Mike!! :)
You are welcome, Gaya!!!!
Haven't worked on Power Query or Pivot Tables yet but still. Love the way you explain them. Thanks Mike.
For people encountering data formating error at the start when filtering store ID:
Don't click "use the first row as headers". First, filter out unwanted store ID rows ("store ID"), then manually change the column names. I don't know what's the exact problem there but i solved it like this.
thank youuu.... this is what i am looking for.. GBU!
Fantastic video on the flow of data to PQ to Data Model to Dashboard!
Glad you like it Doug! : )
I'm always impressed of your professionalism !!!
You are the master of the masters of teaching.
Thanks for all your works.
Now about this video. The step "Promoted Headers" is a little dangerous because you have to be sure that you have in a folder the file which name starts with "01". If not, then you can get an error in "Renamed Columns" step after you add new files to your folder.
For example: the first run the query - you have got only files with more than 05 on the begining of its names. Then, you want to add new files but the name some of them starts with less than 05. This can be a problem.
So, maybe better manually change the headers?
1. Seeking a solution exact to this issue.
2. How do I keep the file name or other file information when importing text files?
Yes, I agree, if the files didn't always follow the pattern "two characters" at beginning, it would not be good. I am not sure what you mean about dangerous to promote headers. Does the danger come from the fact that there might be 1 character to start the file name before the City Name, or 3 characters, or 4?
As always, I appreciate your helping me to get better and learn, Bill Szysz!!! :)
Just remove 01Seattle.xlsx file from folder and see what happens :-)
Replace your code after #"Removed Other Columns1"
#"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Table.PromoteHeaders([Data])),
#"Removed Other Columns2" = Table.RemoveColumns(#"Added Custom1",{"Data"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns2", "Custom", {"Date", "Sales", "StoreID"}, {"Date", "Sales", "StoreID"}),
#"Renamed Column" = Table.RenameColumns(#"Expanded Custom",{{"Name.2", "City"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Column",{{"Date", type date}, {"Sales", type number}, {"StoreID", type text}})
in
#"Changed Type1"
Regards :-)
Look the answer from "Excel 24/7"
"Before you expand the data column you better insert another custom column with the formula: Table.PromoteHeaders. With this extra step only the first row will be a Header and the other headers from the Rest of the tables will automatically ignored."
Thats the solution for the problem with new or different filenames ;)
Liked first.
Thank you very much sir.
You are welcome, Chamchijjie : )
Great video, I love PowerQuery
Glad you like it!
Thank you for these amazing videos. I'm currently using the newest version of Excel 365 and got functions updated. Could you please do a updated video with updated functions? :D Thanks
Cool!
PQ > PP > PT
:)
Yes!!!!!! :)
Hi Mr. ExcelIsfun :) Mike, I do have a question, why didn't u use Promote headers @ 9:54 in here? this would promote every 1st row for all tables(3 on screen) then u would not have to delete headers from data set later on.
Awesome content, really helpful.
Glad it is helpful fro you, Albert! Thanks for your support with your comment, thumbs up and sub : )
Before you expand the data column you better insert another custom column with the formula: Table.PromoteHeaders. With this extra step only the first row will be a Header and the other headers from the Rest of the tables will automatically ignored.
Awesome tip! Thanks!
Thanks! Great tip!
The M Language is a bit tricky for me. Intellisense would be a great feature for that.
Or at least an integrated directory for all these functions. But no matter.
Thanks for the tip. I added an annotation to this video at 10:24 and will show the Table.PromoteHeaders function in the next video. Thanks for being part of such an awesome Online Excel Team!! Thanks for helping me to learn!
I have a question. I have tried to search online and have posted to the Mr Excel Message Board, but I have not found an answer. Question: After you use Excel.Workbook function and then expand to show Name, Data, Item, Kind and Hidden columns, do you know what the difference between the Name column and the Item column is?
Reply
@ExcelIsFun regarding your question;
Item column stores the actual name of the object (sheet, table, define name) in excl file,
While Name column may amend the name of the object if there are any duplicates, see screenshot below
prntscr.com/beoukz
Thanks
You are welcome clif "WRH" ford!!!! : ) WRH = World Record Holder.
Sir, I have Excel 2016 and I can not find Power Pivot in my excel also tried to install from Microsoft website but failed. I humbly request urgent help in this regard.
❤️
Is there a list of most commonly used M formulas in Power Query?
I found a list of all the formulas (a lot), but am looking for most commonly used list.
Have you created such a list Excelisfun?
I had fun.
Yes!!! Love to hear that fun was had ; )
May I ask if there is a know issue with query unable to extract table saved in a XLSB format? XLSB is greatly reducing the size of one file as compared with XLSM, but I keep on getting a DataFormat.Error: External table is not in the expected format regardless of the machine used, any advice?
Hi awesome video! But I have a question everytime a put a new file and refresh my query I always get these message. "Index is out of range(Should be bigger or equal to 0 and smaller than Count)
Parameter name: index
Actual value was 29." Can you please help me on this. Thank You!
At the pivot table, the Dates is net separating or splitting into quarters, years and months, how do I solve this problem, it only occurs when I use the Data model
Great video! i am having some technical difficulties. may you could help me? after loading the data, i am getting an error with no description just error # (0x80004003)E_POINTER.
in the video is the point @12:12
any solutions or anyone came across this issue?
I have query.why you add column in power query.
Why you add custom column in power query.please reply
sir when i tried to filter out the store id and clicked on load more it showed data format error
Amazing video when i load my data into the data model it takes minutes not seconds anybody have any ideas why? I have intel i5 8gb ram 64 bit office
Hi sir.
i am using microsoft 365 version of Excel and it does not group the date by month. is there a way i can do it manually? thank you in advanced.
If you have empty cells or text dates, the grouping feature will NOT work. Then you must fix those. Otherwise, maybe auto group in turned off in options. In that case, right-click in row area where dates are, click on Group, then select what you want, month, year and so on.
When I calculate % of Grand Total from Total sales and name it (% of Total or whatever), DAX measure and Total sales header also automatically change as well. How can I solve this problem?
Hi everyone. Can someone please respond to this question? Thanks.
Is there a list of most commonly used M formulas in Power Query?
I found a list of all the formulas (a lot), but am looking for most commonly used list.
Have you created such a list Excelisfun?