Import Data From Multiple Excel Files Into One Updatable SQL Server Table Without Coding

-


It's a sneaky way to get around coding and using visual studio to create a link between a folder and SQL server but it is as efficient as it can be!

The main idea is using Power BI's Query Editor to do all the boring work of merging data from multiple excel files for us into one table -also you can wrangle your data however you want- then exporting it using Dax Studio, So let's dig into it.

1- You Will Obviously Have To Download Dax Studio  

It is a free 8MBs tool that can be easily downloaded from here: DAX Studio - The ultimate client tool for working with DAX queries
It's easy to install so I'm not gonna explain it to you, sorry.


2- Have A Look On The Data  



So this is our data,
 It's a folder containing multiple
 excel files from which
 we want to extract our data and merge it.

It can be as many as it is 


3- Open The Good Power BI And Get Data


1- Open Power BI
2- Click on 'get data'
3- Select data from 'folder'
4- Select your desired folder and hit ok

This is basically query editor step you should be familiar with 



If you are not familiar with power query editor and how to transform data with it see this topic: The Best Way To Import Data From A Folder To Query Editor Without Getting Any Errors



This is the final result we wish to export to a SQL table 
You can wrangle it, make it as nice and clean as you want

Load the data and we are ready to go



4- Now The 'Dax Studio' Step

from external tools you will find 'Dax Studio' …. If you don't go up there and install it
then run it from external tools 

You can see our data is loaded and we are going to Dax Studio!

Our data is ready to go so from the 'Advanced' tab hit 'Export Data'

Choose 'SQL Tables'




Enter your server name and the database you want your table to be exported to

See that check box? we will explain how useful it can be at the end


Now Export your data 
And here is our good table!





5- So where is exactly the 'Updatable' Part?

Alright alright, I will tell you …
Suppose you opened one of the excel files and did some changes 
all you will have to do is:
1- open the same .pbix file we are using to export data and hit 'Refresh'
2- Export the data using Dax Studio again BUT:
  • Remember the check box? don't ever uncheck it cause that's where you tell SQL Server to delete any tables with the same name and insert the new ones which have our changes.

  • Also, remember that the name of the table is the same name as your query.

Regards.

Comments

Post a Comment

Popular posts from this blog

Get The Distinct Count Of Values By Each Day Using DAX

-
Image
  We have a serious issue when it comes to calculating the distinct count of values based on dates, why? cause if you want to get the distinct count for each day, the usual distinct count measure will ignore the day-by-day calculating and will get the distinct count for the whole period.  so let's see how to overcome this. The solution is simple ... we will create a summarization by date and we will iterate through it calculating the distinct count This is the original measure that calculates the distinct count This is our new adjusted measure it simply defines a variable that holds a summarization of our table by date  and through this summarization , we will iterate and calculate the sum of the distinct count for each day Here we can see that for one day the two measures give the same value and it's reasonable  This is the tricky part that the new measure overcomes, for more days, the old measure gets the unique values for the whole bunch of days, not for each day....
Read more

Integrating Python in Power BI With An Example Of Data Wrangling & Data Visualization

-
Image
  With a mix of Power BI & Excel's versatility and coding experience, You can easily wrangle any kind of data and get the most out of it. Let's see how we can use python in two processes (Data Wrangling & Data Visualization) inside Power BI Make sure that anaconda and python3 installed on your pc. Our Data set The dataset I'm using for this article is a Kaggle dataset that I've recently worked on you can check it out here what we are going to do is to transform some of my work in this notebook to power bi. 2. Data wrangling The data wrangling process in power bi is based inside the query editor After loading your data click on the transform tab then run a python script then you will need to import your libraries and run your code for me, the dataset only needed to replace some values and to get the date and time  for more information check out my Kaggle notebook You will get this after running your code this is the new dataset after running your code  so all you...
Read more

Tips To Overcome Over plotting of Dense Scatter Plots.

-
Image
Here are some tricks to overcome overplotting of dense scatter plots Use a transparency factor: # Matplotlib scatter plot with an alpha value plt . scatter ( df [ X_COL ], df [ Y_COL ], alpha = 0.03 ) plt . xlabel ( X_COL ) plt . ylabel ( Y_COL ) plt . show () This will introduce some transparency to your visual and the less transparency the denser. You can make this even cooler with animated transparency: # Initialize plot and animation camera fig , ( ax1 , ax2 ) = plt . subplots ( 1 , 2 ) # Create 2 camera = Camera ( fig ) # Create a sequence of alpha values alpha_range = np . linspace ( 0.5 , 0 , 30 ) ** 3 # For each alpha value for alpha_value in alpha_range : # Plot "vanilia" plot for reference ax1 . scatter ( df [ X_COL ], df [ Y_COL ], color = 'black' ) # Plot scatter plot with the alpha value ax2 . scatter ( df [ X_COL ], df [ Y_COL ], alpha = alpha_value , color = 'black' ) # Take a ...
Read more