Hello everyone, My name is Mike. In previous video I shared you on how to save
email attachment to one drive by automate flow. In today’s video I shall share you a method
to import the zip file from one drive and unzip it by Power query in Power BI. Ok, Let’s get start. Before import zip file from one drive we need
to have custom function to unzip source file in power BI. Regarding the power query custom function,
we may refer to “Microsoft Docs” by search google. There you will find “using custom functions”
regarding the concept, creation and usage of custom function. For our scenario we may directly refer to
the Mark white BI blog site where we can find the ready to use M code to build a unzip function
in power BI. I have listed the site address in video descriptions
for your reference. In the site you may find background info regarding
the M codes for unzip file, let’s brower the site to the code section, clock DOWNLOAD
RAW, Copy the whole M codes and paste them in your notepad for late use. desktop, click “Get Data”, choose “Blank
query”, click the “Advanced Editor”, Replace and Paste the copied Unzip M code
into the query editor, click “Done”, name the query as “ReadZipFile”, click “Create
& Apply” button,choose “close & Apply”, query is loading. Ok , now our “readZipFile” reuseable custom
function is ready for using in the desktop. Let’s import the zip files stored at one
drive. click “Get Data” button, choose the “More”,
type sharepoint in search box, choose the “Sharepoint folder”, click “done”,
fill in the one drive site address, Power BI load all files stored at the one drive
into the preview, click the “transform” button, query loads all files. Filter the files that have only the zip extension. Then in the folder path column select the
folder which name contains “OneDrive test folder”. now in query editor power BI only lists the
zip files we have saved at one drive. For the query we want the info from latest
zip file which name is “Demo_csv.zip”. the filtered file content is binary format. Let keep only the Content and Name two columns. Now we need to invoke the created custom function
Click the “Add Column” button in top bar menu. Click the “Invoke Custom Function”, in
popup window we see the “custom” as default name for the new column, scroll down the function
query list and select the “ReadZipFile”, Click OK,
In this screen we keep the ReadZipFile column and Name column, and expand the table content
of the “ReadZipFile” column, then tick out the “Use original column name as prefix”,
click OK, click the expansion on the content column, now the query function is starting
unzip the file . Click the OK in combine Files window, we see
the unzipped file in table format. Click Home button in top menu, click the remove
rows button, choose the “Remove Top Rows”, we should remove first 5 rows, fill 5 in,
under transform top menu, choose the “Use First Row as Header”. Power query transforms a clean table with
headers for us. Name the query as “Demo_csv_table”, Press
“Close&Apply” button to load the table into power BI desktop. Let’s move to the Table view of Power BI
desktop we can see the table loaded there. Here I like to share one way to get the latest
update date from the table for reporting and dashboard creation. Let ‘s right click on the Demo_csv_table,
choose the “Edit query”, on the left hand side right click on the “Demo_csv_table”
and choose the “Duplicate”, then we get duplicated Demo_csv_table” in query editor,
we remove the last three query steps. We see a table which has the latest update
date in row 3. Now we just need to keep row 3 from the quired
table by clicking”Keep Rows” and choose the “Keep Range of Row” by filling 3 and
1 in the required boxes., click OK. We see the quired table which only contains
the latest updated date. Remove other columns, name the column headers
and table. Close and Apply to load the table into desktop. Now we have two tables . one is Demo_csv_table. Another one is Demo_csv_Updatedate table. By this way I have created other two queries
to import all backup files that stored at one drive . and name the desktop as “Automation
PBI and Flow Case 1”, in the desktop I have 4 fact tables , two for Latest file and two
for all backup files. In data model view I built a simple data model
, in visualization view I have two report pages as I demonstrated in previous video. Now let’s publish it to Power BI service
in my workspace . save and publish. Then it shows the publish is successful. Let’s go to the power BI services, click
the “Datasets+dataflows” we see the dataset of “Automation PBI and Flow Case 1”. this
is the dataset we shall create our second flow to refresh it automatically when new
file saved at one drive. By now we knew in Power BI how to import and
unzip files from one drive. In next video I shall demonstrate on how to
create the flow to refresh the power BI dataset when new file saved at one drive. Thank you for watching, if you like this video,
please give your thumb up, If you want to watch more videos please hit the subscribe
button. Thanks again and see you in next video.
