Thinking of full outer join in Power BI what comes first in your mind? How can we achieve full outer join in Power BI? Common Answer will be the “Use Merge Query” Option in Power Query Window. However, I would like to tell you that we can use DAX to achieve Full Outer Join. Full Outer Join = left Outer Join + right Anti Join Customer Table: Order Table: Click on new table and write the below DAX: DAX for Full Outer Join of Customer and Order Table: Result: Hope this helps!! Thank You!!

In Power BI Desktop app and when we edit the report on Power BI workspace, page tab visible at bottom, but when we publish the report on to the Power BI Service then it is visible on left side. We can change the Tab Position in the Power BI web as well, to do that we can follow below steps. 1.Go to report setting 2.Enable the option of Pages Pane enable the button and save the changes. And when we open the report tabs will be visible at the bottom. Hope this helps!

After PowerAutomate was added in PowerBI as a preview feature, it is finally available for general use. In this blog we are going to use this PowerAutomate visual to export PowerBI data into CSV and upload the CSV on Sharepoint. Open PowerBI Desktop and drag the PowerAutomate Visual, if it isn’t available in the visualization pane update your PowerBI Version. Now add the columns, you need in your CSV file. Click on the ellipsis button on the visual and select edit. Create new flow and select instant cloud flow. A default PowerBI trigger would be created, click on add new step. Search “Compose” action in the search bar and select it as we have to compose PowerBI Raw data first. In the Inputs select PowerBI Data. After composing we have to convert this data into CSV, therefore add “Create a CSV table” step and add previous output. Now we have to upload this CSV into sharepoint so we will add a step to create a sharepoint file. Enter the Sharepoint address, Folder Path, give the file any name desired and put “.CSV” as suffix. Save and close the map. To run the flow, press Ctrl and left click. As you can see the file in SharePoint is created. Thanks for reading, hope this blog helped!

Hi in this blog we will see how we can add rows to power bi dataset for a given date range. For example if you have a dataset which has start and end date and you want rows for each date between this range then this blog will help you. Step 1: Open Power BI load your dataset and go to transform data. Right click on your from date column and change its type to date. Step 2: From add columns click on custom column Step 3: In custom column formula add following code: { Number.From([From])..Number.From([To]) } Step 4: Expand this column to new rows to get your result. Step 5: Change the datatype of this column to date. In this way you can prepare your dataset for all the dates present in your From and To column. Hope this helps.

Consider a scenario where a start and end date along with the total duration for a particular task has been given to us. We have to distribute the total duration equally among all dates between the start and end date. We can solve this issue by the combination of Power Query and DAX. Lets see the steps First we need to generate the list of dates from start date till end date. Open Advance Editor As you can see the table I’m working on have two columns for start and end date for a particular task respectively. In Power Query we cannot generate a list between dates so first we have to convert the data type from date to numeric. This can be easily done by right clicking the desired column and changing the data type right away. After the data type of respective columns is changed into numeric, click on the “Add Column” option in the ribbon and select custom column. To generate a list in Power Query the syntax is “starting number .. Ending number” so we apply this syntax in accordance to our needs. The “Number” function is make to sure to take only numeric values to avoid any conflicts. After validating the code press the “OK” button. You can see a new column with lists. Click on the Expand button on the top right of the column. After Expanding the column you’ll see a list of numbers. Since these numbers are numeric we have to again convert them in Date format. This can be done by right clicking on the column and changing its Data Type. As you can see we can see all the dates between start and end dates now. 2. Since we generated the list of dates. We Proceed to distribute duration equally Create a new Calculated Column in PowerBI Desktop Write the following DAX. actual hours = Sheet1[original estimate]/ CALCULATE(count(Sheet1[taskid]),FILTER(Sheet1,Sheet1[taskid]=EARLIER(Sheet1[taskid]))) This code divides the Duration assigned for a task by the count of the total tasks where the task id is same. As you can see the Original Estimate column which is for total duration for a task is equally divided into a new column called “actual hours”. You cant see dates of Saturday, Sunday as I filtered these dates in Advanced Editor itself as they are non working days. This can be modified according to requirement. Thank you for reading hope this article helped

While importing data into PowerBI we can choose the tables we want to load in the data model but we cant choose specific columns, although columns can be removed through advanced editor or by manually deleting them one by one this can also be done without using advance editor and multiple columns or tables can be deleted at once. To do this go into the data model section of your PowerBI report. Now expand the desired table and the press ctrl key on your keyboard and select the columns you want to delete. You can also select multiple tables using the same instruction. After selecting multiples columns or tables right click and select “Delete from the model” option. In the dialogue box that appears click on the “Delete” button. Then click on “Apply Changes” option which will appear in the window and you can see all those columns are now deleted Hope this helps!

Sometimes we would want to toggle some visuals in PowerBI on the basis of a button click for instance when clicking a button a certain visual shows up and while clicking the same or another button the visual hides itself. This can be easily done with the help of bookmarks in PowerBI. In this blog we will hide and show the slicer panel. First let’s insert a right arrow button from the Buttons option in the insert tab. And place this newly created button in the extreme left of our report canvas. Now click on View tab from the Ribbon and enable Bookmarks Pane and Selection Pane. Click on Add option in Bookmarks and create a new bookmark named Slicer Invisble Now again go to Insert tab and from shapes draw a Rectangle over the button. Now on the rectangle add a slicer and populate it with desired values also add a left button so as to close the slicer pane. Now add another bookmark named Slicer Visible in the Bookmarks pane. Now in the selection pane hide the first button we had created and update the Slicer Visible Bookmark. Similarly keep the first button visible and keep the shape, slicer, and second button hidden. Update the Slicer Invisible bookmark accordingly. Right click on both the bookmarks and uncheck the Data option so as to not record changes in data. Select the second button and toggle the Action button as on from type select bookmark and from bookmark select slicer Invisible. Similarly click on the first button and set its action to slicer visible bookmark The toggle buttons will open and close the slicer pane as required. Thank you for reading my blog hope it helped.

Sometimes we would want to ask users about their thoughts , opinions or ask some questions along with a PowerBI report. That is, we would need  to collect information about the user through PowerBI App.  This can be done by creating a Microsoft Form inside PowerBI interface to collect user information. This can be demonstrated as: Go to forms.office.com and create a new form. Insert the Title , Description and add some fields in my case I have created a Test form with a field for giving ratings. Click on share and copy the link. Go to app.powerbi.com , create a new workspace and while creating enable develop a template app option Add your existing PowerBI reports here by clicking “New” option and then click on create app. You can add Name, description and logo to this app also you can choose a theme Click on Navigation tab and enable new navigation builder Click on the  New button on the navigation pane and select link. Enter the Name of the form and enter Microsoft form link Publish the app then select go to app As you can see the form is embedded in PowerBI interface The responses done by users gets saved at forms.office.com This can be used in a scenario where we need feedback for a  PowerBI report Hope this blog helped Note: Not all Power BI users can view and interact with apps. If you have a free license, you can open apps that are stored in Premium capacity and have then been shared with you. If you have a Pro license, you can open apps that have been shared with you.

Introduction: Sometimes while refreshing our dataset in PowerBI or importing new data from existing sources we may encounter OLE DB or ODBC errors as shown in the image below. This might be due to caching issues. To solve this follow the steps: Go to File tab on the ribbon in PowerBI Desktop , then click on About and check the PowerBI version, make sure it is the latest version if not update PowerBI. If PowerBI version is latest, click on the dropdown arrow below the Transform Data button. Click on Data source settings Click on Global Permissions Select the Data Source and click on Clear Permissions and click on close. Click on close and again select the data source as new and enter the credentials. This time the dataset would be loaded without errors. Hope this article helped

Part 4: Testing using Developer Portal and JWT Policy Configuration Introduction Configuring OAuth 2.0 for your APIs hosted in Azure API Management adds an extra layer of security and prevents unauthorized access. This is a very important configuration form Security point of view for your Endpoints and is provided out of the box by Azure. This is the second part of a series of Blogs on Securing your API using OAuth 2.0 in Azure API Management. Please go through all the parts to find easy and detailed steps that will help you configure the OAuth 2.0 Authentication. Successfully call the API from the developer portal Note: This section is new to the Developer Portal and is under changes. So sometimes it might give Un-Authorized or CORS error. Hopefully, this bug gets resolved in future soon. Now that the OAuth 2.0 user authorization is enabled on your API, the Developer Console will obtain an access token on behalf of the user, before calling the API. Browse to any operation under the API in the developer portal and select Try it. This brings you to the Developer Console. Note a new item in the Authorization section, corresponding to the authorization server you just added. Select Authorization code from the authorization drop-down list, and you are prompted to sign in to the Azure AD tenant. If you are already signed in with the account, you might not be prompted. After successful sign-in, an Authorization header is added to the request, with an access token from Azure AD. Sign in to the portal Click on accept Note an Authorization header is added to the request The following is a sample token (Base64 encoded): Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImtpZCI6ImppYk5ia0ZTU2JteFBZck45Q0ZxUms0SzRndyJ9.eyJhdWQiOiJkMTYyODJhYy05N2RlLTRlNGYtODVkZi0xNmVlZDUwNjNjNWUiLCJpc3MiOiJodHRwczovL2xvZ2luLm1pY3Jvc29mdG9ubGluZS5jb20vMjZjNGIyZTQtZWMwNy00YzdiLTkyZTUtOTdmNTI4NjVlOThiL3YyLjAiLCJpYXQiOjE2MDEyOTIxODYsIm5iZiI6MTYwMTI5MjE4NiwiZXhwIjoxNjAxMjk2MDg2LCJhaW8iOiJBVlFBcS84UkFBQUE4RmJoME4rOHFhVVpZSVRDY1hBVXVNOXZMOVNleUhHWnJnSUtiOUJkNW9HWEpBS1ArMEl5Q1FmUGx2NWo0amIxL0tKa0dLeHFOeWRDZlk3cTN3NGMzbHZsK3ovSFl3VmljNHJKMTBPakFsYz0iLCJhenAiOiIzYzcyZjU3ZC04M2YxLTQxMzktOTIzMi05YWFlODNjZTY2NjQiLCJhenBhY3IiOiIxIiwibmFtZSI6IlJ1c2hhbmsgS2FyZWthciIsIm9pZCI6IjU3Mzc4NTNkLTVhZTEtNDU4Ni05YjUzLTE5OWI0NDcyYWVkNyIsInByZWZlcnJlZF91c2VybmFtZSI6InJrYXJla2FyQGNsb3VkZnJvbnRzLmNvbSIsInJoIjoiMC5BQUFBNUxMRUpnZnNlMHlTNVpmMUtHWHBpMzMxY2p6eGd6bEJrakthcm9QT1ptUlVBQTQuIiwic2NwIjoiRmlsZXMuUmVhZCIsInN1YiI6IjY5UGhwWTFKNm1qS3ZhT1FaeDdaNm1jM2hpV2RVUXFoVTZpczZPdVdmZXMiLCJ0aWQiOiIyNmM0YjJlNC1lYzA3LTRjN2ItOTJlNS05N2Y1Mjg2NWU5OGIiLCJ1dGkiOiI5UTB0S1hBWkFreWpMdGw5TE9nNUFBIiwidmVyIjoiMi4wIn0.S-Wx7iH8TK4aW2Wi2msP6wu__oAmUdvpFAaAZi3_n_by2C7ElJRSeBvjYwVImsEzw4gg1zGm6ssH0xUcj2YJ3tZ5ddFW8IZR1DICHvT_sUXFCrRg6ZDlj_VPzeyLor_RmabgSE4ZfMQOmrfCET51AnQHS96-lIp_cB6SkddWQielfQMebhMMVPNyjLsBcSmFxY4gk0e3cEWnGPQHQRRMxwnJnJeqv8Gfm4fMD_xwD05nGyQ3M_mZt3H0UZiLjvVwsRlS9t7MPhVJCZPZBxEIkg0U_2IOE9OQEmuKwdyBLjVM8dNFUxfEOFUvoYUvJ-hd8hwxB5CXBYdQG9kLiAJpOg Select Send, and you can call the API successfully. Configure a JWT validation policy to pre-authorize requests At this point, when a user tries to make a call from the Developer Console, the user is prompted to sign in. The Developer Console obtains an access token on behalf of the user and includes the token in the request made to the API. 1. However, what if someone calls your API without a token or with an invalid token? For example, try to call the API without the Authorization header, the call will still go through. The reason is that API Management does not validate the access token at this point. It simply passes the Authorization header to the back-end API. You can use the Validate JWT policy to pre-authorize requests in API Management, by validating the access tokens of each incoming request. If a request does not have a valid token, API Management blocks it. For example, add the following policy to the <inbound> policy section of the Echo API. It checks the audience claim in an access token, and returns an error message if the token is not valid. For information on how to configure policies, see Set or edit policies. <validate-jwt header-name=”Authorization” failed-validation-httpcode=”401″ failed-validation-error-message=”Unauthorized. Access token is missing or invalid.”>     <openid-config url=”https://login.microsoftonline.com/{aad-tenant}/.well-known/openid-configuration” />     <required-claims>         <claim name=”aud”>             <value>{Application ID of backend-app}</value>         </claim>     </required-claims> </validate-jwt>  Note This openid-config URL corresponds to the v1 endpoint. For the v2 openid-config endpoint, use https://login.microsoftonline.com/common/v2.0/.well-known/openid-configuration. For our scenario the XML Policy is as follows: <validate-jwt header-name=”Authorization” failed-validation-httpcode=”401″ failed-validation-error-message=”Unauthorized. Access token is missing or invalid.”>     <openid-config url=”https://login.microsoftonline.com/common/v2.0/.well-known/openid-configuration” />     <required-claims>         <claim name=”aud”>             <value>d16282ac-97de-4e4f-85df-16eed5063c5e</value>         </claim>     </required-claims> </validate-jwt> Now go back to the developer console and without selecting the Authorization Code try to click on send. The Error Message is displayed. Reference Link: https://docs.microsoft.com/en-us/azure/api-management/api-management-howto-protect-backend-with-aad Part 1: Configuration of Applications in Azure AD  Part 2: Configuration of an Application (Client App) in Azure AD for Consumer  Part 3: OAUTH 2.0 Server setup  Part 4: Testing using Developer Portal and JWT Policy Configuration