Do you need to extract some of your Power
BI data and analyse it in Excel? Don’t miss this video! Hello everyone, Shahab is back with another
interesting video. It’s highly possible that your manager asks
you to download the data behind your visualsations. Maybe they want to do some further analysis. There are different ways to do that. For example, you can create a button to download
the Excel file into SharePoint. But because of the security in many of the
organisations maybe they block you to do that. So, what's the solution? In this video I will show you how you can
send the data behind your visualisations in a form of a CSV file. So, watch this video up to the end, and don't
miss any step. If it’s the first time you’re watching
my videos, please subscribe to the channel to get the
notification for the new upcoming videos! You can also follow me on LinkedIn and read
Power BI articles on my blog. Stay Tuned! Consider we have a report like this one, and the same as many other reports you can
interact with slicers and also you can easily select a category
or some specific values on visuals. This is really cool! But your report users asked you how we can
download and extract the data available on this table
to play, check, and analyse! Here is where the problem arises! Because Power BI won’t allow your end users to download some part of data based on their
selections. Don’t worry! I’m here to teach you how to deal with this
issue! So, what’s the solution? The solution is using Power Automate in Power
BI. Yes, that’s correct! Power Automate helps us to extract the selected
data and use it for other purposes. Let’s see how we can use it. Fortunately, since last year, Power Automate
visual is available in Power BI visualisation list, and we don’t need to go to Power Automate
website to create many of the manual flows that should be performed by pressing a button, and we can create them directly in Power BI. The first step is to choose Power Automate
visual. When I click on it, you can see this visual
on the canvas, but to know how I can use it, take a little
bit of time, and watch this video. To start with Power Automate, the first thing
we need is to import the required columns into the visual data field. OK, let’s go back to this page. Consider we want to extract the data from
this table for our users. So, instead of creating a new Power Automate
visual, and select every column one by one and drag
them into the data field, I just click on this table visual, and copy it by pressing CTRL + C and paste
it again by pressing CTRL + V. As you can see, a copy of this table created, and I can move it and change the size. But it’s exactly similar to the other table. Now, what I have to do is when this table
is selected, just click on the Power Automate visual, and
that’s it. You can see this table changed to a Power
Automate visual and all the data fields are now available
here in data field. So easy! You can also add any other field you like. But for now, we don’t need to use any extra
fields and these fields are enough for our demonstration. I just resize and place the visual where I
like. Then, we need to press on these 3 dots, and
choose edit. In the new window, you can see here previously
created flows, or here below you can see the templates to
use. But what we need to do is pressing on New
and then Instant Cloud flow. Then on this new page, we need to add a new
step, and here search for “Initialize variable”. Then, we have two fields here. First, we should choose a name for it. For example, I choose TableContent. The second field is where we need to choose
the variable type. In this field we choose “Array”. This is very important to create a variable
with array format type. Because at the end our data fields in CSV
file will be placed in different columns which is very easy for end users to work with. Otherwise, if you simply create a CSV file, you will receive a simple comma separated
values file in only one column, and you have to split the cells which is really
hard for someone who is not expert with Excel. Next, we need to add a new step, and search
for “Append to array variable”. After selecting it, in the first field we
choose “TableContent”, what we created earlier. The value field is where we need to define
the values based on a Json format. So, I open a curly bracket. Press enter for 2 times and then close the
curly bracket. The values should be inserted between two
curly brackets. In Jason files, we need to define each value
in the form of a pair. The first part of a pair defines the name
of that attribute, and the second one defines the value. For example, here you can see the first data
field is Product Name. So, here inside the double quotation mark, I write Product Name, then, I put a colon, and open and close double quotation again. In this second one I should insert the value. So, at this stage if dynamic content was open
for you that’s fine but if not, click on Add dynamic content, and then you can see the all these data fields
available here. What I need to do is just simply scroll up
or down to find the Product Name from these dynamic content fields and will choose it to place inside the second
quotation marks. It is how easily we can create a pair of values. Then to add another pair we just put a comma
at the end of this line and do the same process until we add all desired
columns. But notice that the last pair doesn’t need
any comma after it. Then we need to add a new step and search
for “Create CSV table” and then, choose “TableContent” variable
from the list. Up to here, we could create a CSV table in
the form of array, exactly what we need. Now, I’ll add a new step and search for
“Send an Email (V2)”. When selected, in the “To” section, I
add “User Email” from Power BI dynamic content. This will allow us to send email to whoever
press the button. In the subject field, we choose a subject
for our email. And, also add a content for it. By clicking on “Show Advanced option”
you can see more details. For example, you can define sending an email
to a person as CC. And here in the Attachment Name section, I
choose a name for our CSV file. Then, write dot CSV to define the format of
the file as CSV. And finally in attachment content I choose
“Output” as the output in previous step. I just press on Save. And then choose a name related to our flow
from here. When finished just press on Save and Apply
and then, go back to the report. Now here you can see the Power Automat visual
shape changed to a button. You can edit the format like many other visual
and it’s out of scope of this video. If you apply any slicer or choose any record
you can only export those ones. For example, here I choose these few rows
by holding the CTRL button and selecting them. When my selection finished, I will click on
the button. As you can see, first it shows triggering
and then after some seconds, you can see it shows triggered. So now I open my email, and wait for some
seconds, and here it is. I received an email, with the content we like,
and a CSV file attached to it. Let me download and open it. And that’s it. It’s exactly what we are looking for. If you enjoyed this video, don’t forget
to like, and share it. And hit the subscribe button for the new upcoming
videos! You can follow me on LinkedIn and read Power
BI articles on my blog. Have a good day all Power BI lovers!
