Excel Magic Trick 1351: Power Query: Merge Text & Excel Files, Custom Columns, Total Row & Slicer

Download Files:

Using Power Query (Get & Transform), see how to Import & Merge Text & Excel Files, Clean Data, Add Helper Columns for Company Name and Revenue, and add a Total Row & Slicer for Power Query Report:
1. (00:16) Introduction and View of Final Report
2. (02:13) Import Text Files with from Folder option
3. (05:40) Clean Data with Split Feature
4. (06:20) Create Custom Columns (Helper Columns) for Company Name (if then else Function)
5. (08:50) Import Excel Table
6. (10:00) Merge Units Table and Price Table using Left Outer Join (like VLOOKUP in Excel or Relationships in Power Pivot)
7. (12:17) Create Custom Columns (Helper Columns) for Revenue (Number.Round function)
8. (13:55) Edit Query when a Field name is misspelled
9. (15:17) Group By Product & Company Name to get Total Revenue
10. (16:52) Load to Excel Sheet
11. (17:55) Add Totals Row
12. (18:34) Add Slicer
13. (19:26) Why we use Power Query
14. (19:48) Add new Text Files to Folder and Update (Magic of Power Query)
15. (20:53) Summary

[Steve_WIEM]

I also have a lack of words to express how cool this video is. There are some freaky things going on here. Beautiful work.

[jonathancooper]

I knew you could use slicers for pivots but didn't know it worked for tables. Thank you. Also, great demonstration of the versatility of PQ.

[zaighamuddinfarooqui]

Great job. Suffering from "Lack of words" to appreciate your efforts.

[douglaszulu]

Very interesting video and demonstration of PQ.

[marekzadecki]

Thanks for you stunning Power Query lesson

[entertainmentgalaxy]

you rocked again. Thanks alot for sharing. Best thing about PQ when you add a new file and it merges into current file within a second....

[dubinlimerick]

Awesome presentation, dude.  Using Excel 2010, I cannot find the slicers so I presume it is with 2013 and 2013 editions.Thanks again

[barttitulaerexcelbart]

Thank you Mike, very good video again. But may I make 3 remarks/suggestions?

1) I personaly like the different default colours for tables: blue for data tables and green for PQ results. I keep them this way so I see the difference.

2) If I use other totals in the summary row, it will not remain when I update, maybe only the last/right column SUM ?

3) If you use Excel data tables as source, you might want to check and also use a total row. Do you know a general method that always works to avoid importing the total row. For example a user can also use the first column and replace the word "total" for a number or somthing esle.... thank you.

[afedianine]

Awesome class, thank you! Can we explore what "Manage Parameters" option is used for on main ribbon? Thank you!

[nicolejonsson]

hi! great as always! bu how can i "merge and center"? i have 20 rows in one column with the same text, but only want one big block covering all rows, and the text only written once. is there a way?

[xlzqicf]

How can i add conumn (File nemes) during merge txt files?

[sabrinasharmin]

.txt file missing, can any one please upload those files in comment section? Thanks

[marcozacarias]

I have a large excel doc with many text boxes, how can I find text and replace that text within the text boxes? I'm thinking with a macro but that's s little above my scope. Help please?

[shuyukahn]

I've tried different ways with different files, but "Close & Load To..." is always greyed out. What could be the issues? (using Microsoft Office 365)

[muhammadfathi]

Thanks for you stunning Power Query lesson