Resources for use with PowerBI. This repo contains :
- Dax measures
- M query functions
- Generic m query code snippets
- Custom connectors to unsupported data sources
Some of the code in this repo is altered or copied from articles online. Where possible sources have been included.
This measure will allow charting of the last known values over time as opposed to only charting values that match the date.
Source : Reddit user u/Aklur created this code and his explanation of how it works can be found at https://www.reddit.com/r/PowerBI/comments/gixdk9/need_urgent_help_with_a_dax_measure/
This measure is designed for use where we want to count all rows (with filters available to be defined) then determine the percent Grand Total within categories. The primary reason to use this was to show "No Data Items" while displaying %GT as the data label within a bar chart.
Sharepoint Data sources have an issue where they do not account for daylight savings. Dates before clock change will be correct but after will be the previous date but 23:00. This code provides a fix for this in M code.
This provides an example of how to make an status index column in dax for use with KPI visuals etc.
Arc Data sources will provide dates in Epoch format. This m code will convert this to a datetime format the PowerBI can use correctly.
Code that can be pasted into the add column dialogue to convert HTML rich text and special characters to plain text.
Source : based around the content of Dhruvin Shah's youtube video found at https://www.youtube.com/watch?v=4UDynnPQpG4
Function varients of code found in other sections. To use these code snippets create a blank query and paste the full code in. Retitle the query and use the new title of the query to call the function from other queries.
Function that will convert the epoch date format from Arc datasources when called with the correct parameters.
Function that converts html rich text in a column to plain text. Converts special characters as well as removing HTML tags.
Source : based around the content of Dhruvin Shah's youtube video found at https://www.youtube.com/watch?v=4UDynnPQpG4
Function that expands all columns for all records in a column of records. Protects against column changes in records.
Source : adapted from Parker Steven's blog at https://bielite.com/blog/dynamically-expand-all-columns/ into a function
Function that corrects errors caused by sharepoint date only columns and daylight savings.
Function that retrieves version history from SharePoint for a column of item ids.
Function that returns email validation data fo a column of emails using the api at https://github.com/CodeKJ/DISIFY. This API does not have a 100% success rate. It attempts to validate the format of the email address only. Some bizarre email addresses may return false results.
This function will work on columns with dates in the following formats (the formats output in the summary part of the timeperiod record returned in the get request for PI data from the Pentana API)
- MONTH YEAR e.g November 2020
- STARTYEAR/ABREVIATEDENDYEAR e.g 2019/20
- QUARTERNUMBER STARTYEAR/ABREVIATEDENDYEAR e.g Q1 2019/20
The split year start is designated as the 1st of April
Function that will get a value from a previous row in the specified column and add this for each row as a new column. It also allows a groupby column that resets the collected value to null for the first item in each group
!!! Rows must be sorted appropriately before calling the function
Custom Connectors for unsupported data sources. To use these data connectors follow the steps below for each connector.
1. Download .mez file
2. Move to My Documents\Microsoft Power BI Desktop\Custom Connectors - If this directory doesnt exist please create it.
3. The data can now be accessed using the Get Data menu in PowerBI
!!! Some of these connectors potentially expose client secret information if traffic is monitored using fiddler !!!
Retrieves a table of performance data for each indicator selected. Notes are not currently available from the Pentana API neither are variances for each period.
Within the .m file contained in the .mez the url variables below will need to be adjusted to reference your particular organisation's url
- token_uri = "https://YOURORGANISATION.pentanarpm.uk/cpmweb/oauth/token";
- pis_list_uri = "https://YOURORGANISATION.pentanarpm.uk/cpmweb/api/pis";