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

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

REDUCE Your Measures With Calculation Groups In Power BI

-
Image
  You will need an external tool Tabular Editor for this. If you already don't have it, then please install it first. You can visit the Tabular Editor website to download the latest installer. Calculation groups are a new feature in DAX, inspired by a similar feature available in MDX known as calculated members. Calculation groups are easy to use; however, designing a model with calculation groups correctly can be challenging when you create multiple calculation groups or when you use calculation items in measures. Let's see first why calculation groups are useful: Imagine we have these measures: Sales Amount   := SUMX ( Sales , Sales[Quantity] * Sales[Net Price] ) Total Cost     := SUMX ( Sales , Sales[Quantity] * Sales[Unit Cost] ) Margin         := [Sales Amount] - [Total Cost] Sales Quantity := SUM ( Sales[Quantity] ) And we want to calculate YTD, MTd ...etc for these measures: YTD Sales Amount := CALCULATE (      [Sales Amount] ,      DATESYTD ( 
Read more

شرح الأداة الرائعة Tabular Editor للـ Power BI

-
Image
  مميزات أداة Tabular Editor للـ Power BI : يمكنك نسخ الـ Measures من تقرير لتقرير اخر بكل سهولة يمكنك عمل Calculation groups والتي ستقلل عدد الـ Measures في التقرير يمكنك عمل Folders لتقسيم الـ Measures بشكل واضح يمكنك عمل مئات الـ Measures بسهولة بدون ان يحتاج التقرير للتحميل عند كل مرة والذي سيوفر عليك وقت كبير! أولا لتحميل الاداة من Github اضغط  هنا  نسخ الـ Measures من تقرير لتقرير اخر بكل سهولة: يمكن نسخ جميع الـ Measures الخاصة بك من تقرير لاخر بشرط ان يكون التقريران بهما نفس البيانات لمعرفة الطريقة من هنا .  عمل Calculation groups والتي ستقلل عدد الـ Measures في التقرير: الـ Calculation groups هي طريقة لتقليل عدد الـ Measures فمثلا اذا كان عندك Measures لحساب قيم معينة وتريد ان تحسب كل القيم Year to Date أو Month To date وهكذا … بدلا من ان تنسخ كل هذه الـ Measures وتطبق عليها فلتر DATESMTD أو DATESYTD يمكن عمل calculation item والذي بدوره سيطبق هذا الفلتر على كل  الـ Measures  لمعرفة الطريقة من  هنا . عمل Folders لتقسيم الـ Measures بشكل واضح: يمكنك تقسم جميع الـ Measures الخاص
Read more