Excel Power Query #05: Create Dimension Table From Fact Table for PowerPivot During CSV File Import

See how to Create Dimension Table From Fact Table for PowerPivot During CSV File Import:
1. (00:08 minute mark) Problem Setup
2. (01:33 minute mark) Create Dimension Table or Lookup Table from File Name in Fact Table. Use Power Query and From File / From Folder.
3. (02:33 minute mark) Add Index Column.
4. (03:05 minute mark) Import Fact Table. Use Power Query and From File / From Folder.
5. (04:31 minute mark) Merge Tables to Allow CityID (Index) to populate in Fact Table for a relationship later in PowerPivot.
6. (05:35 minute mark) Load tables to Data Model in PowerPivot.
7. (06:07 minute mark) Add Formatting to tables in PowerPivot.
8. (06:20 minute mark) Create Relationship in PowerPivot.
9. (06:48 minute mark) Create Pilotable with Slicer.
10. (07:49 minute mark) Add New Files to Folder

[excelisfun]

Excel Power Query #05: Create Dimension Table From Fact Table for PowerPivot During CSV File Import

See how to Create Dimension Table From Fact Table for PowerPivot During CSV File Import:
1. (00:08 minute mark) Problem Setup
2. (01:33 minute mark) Create Dimension Table or Lookup Table from File Name in Fact Table. Use Power Query and From File / From Folder.
3. (02:33 minute mark) Add Index Column.
4. (03:05 minute mark) Import Fact Table. Use Power Query and From File / From Folder.
5. (04:31 minute mark) Merge Tables to Allow CityID (Index) to populate in Fact Table for a relationship later in PowerPivot.
6. (05:35 minute mark) Load tables to Data Model in PowerPivot.
7. (06:07 minute mark) Add Formatting to tables in PowerPivot.
8. (06:20 minute mark) Create Relationship in PowerPivot.
9. (06:48 minute mark) Create Pilotable with Slicer.
10. (07:49 minute mark) Add New Files to Folder

[uniQue_XL]

Awesome!
The ability to link XL file to a particular location with the data and make the XL file to be updated when something on that location is change is extends the capabilities of Excel to new heights! realy loved it !

[every-dayjoe]

Hi,
Just wanted to say thanks for all of your videos. Please note that for excel 2013 Power Query and Power Pivot are only available for the following versions of excel: Professional Plus, or Office 365 nothing else! I have 2013 Professional on my desktop at work and I had to up grade to Professional Plus to get it, Very frustrating since I knew how powerful this tool

[amrgaber]

thanks
you teach us how make life easy :D
really thank you
and my question can work with just excel in company while studying ?

[markfarmer]

Great videos. I have a large data set which I need to split into tables and add relationships. I am struggling to separate out the more complex data. Please help me!!!

[Steven-xfmz]

I'm slightly on connection "dcitytable" I am under the impression that it's just a listing after all the manipulation but it looks like it's still connected to the original folder where it was originally imported from, hence, when the new files are imported dcitytable is updated with the new cities. Am I getting this right?

[ashishtiwari]

Hi mike,
I am using Excel 2016.I downloaded the files and checked the csv files.The sales column was in General format.I tried changing it into number format and when I tried to save it, it displayed a warning message:"Some features in your workbook might be lost if you save it as CSV".Anyways I saved it and imported using power query.In the power query editor window, it didn't recognized the sales column as a number format.In the ribbon, it displayed it as a text format and when I tried to change it into number format, the sales column displayed an error.Can you please help me out?
thanx

[cliftonmcreynolds]

Must each of your worksheets have a column (in your example) to build a relationship to? Is there a way to populate that as you build your query?

It's Friday and my brain is mush, so let me explain:
I'm trying to combine client data existing in several xlsx files but the only distinguishing element is the file name. Do I have to manually go into each Excel file and add a column with the client code? Or is there a way as I merge the tables to populate with the client code? I know I can build an index table from file names and have done that, but there's nothing to index to to separate them.

I hope to goodness this makes sense. Thanks for your help, ExcelIsFun 

[douglaszulu]

I would like to use the file name as a field name in the data imported from folder containing csv files. when I expand the binary it is not loading the file name I left along side the binary column. need help

[abhaygadiya]

Few Questions: 1) Can we have Index columns starting with 1, 2, 3, 4....and not with 0?
2) Why dCityTable was not loaded into data model initially itself rather than doing it later? Does it have any impact?

[m-b-m]

I save a new csv file but the power query doesn't refresh the new file. Why....?

Sorry. I don't speak and write english correctly.... :)
:) Help me please, ExcelFun!

[nishantkumar]

What if my folder path or file path is changed?

[troyridley]

I have many questions.  How can I contact you?

[lorenzofeijoo]

For some reason all i see is a green screen.