#ProjectOnline #PowerBI Currency Conversion Project Cost Report Part 2 #PPM #BI #Office365 #PowerQuery

March 9, 2017 at 5:32 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | Leave a comment
Tags: , , , , ,

Following on from my first post on currency conversion found below:

https://pwmather.wordpress.com/2017/03/06/projectonline-powerbi-currency-conversion-project-cost-report-part-1-ppm-bi-office365/

This post walks through a different option for working with multiple currencies. This post will create a similar report as seen below:

image

This report enables the project cost to be calculated based on project currency and rate for the year. In this example I have two projects that should be reporting costs in Euros,the PWA site is set up using Pounds (GBP) as are the resources that are used on those projects. So for those two projects in PWA the projects display a EUR symbol but there is no conversion to calculate the Euro rate from the GBP resource rates used.

In the steps below we walkthrough how to set up this example. Firstly in the Power BI Desktop client add the Projects OData feed:

  • Click Get Data > OData Feed and add the Odata URL for your PWA site: <PWASite>/_api/ProjectData/Projects and click OK
  • Click Edit to launch the Power BI Query Editor then click Choose Columns and uncheck Select All to deselect all the columns then select at least ProjectId, ProjectName, ProjectCurrency and ProjectType and click OK
  • Click the dropdown menu on the ProjectType column and uncheck 7.
  • Change the table from Query1 to Projects

The Projects table is now completed.

Now we need to create a currency table, still within the Query Editor see these steps:

  • Click Enter Data and create 4 columns, Currency, Master, Date and Rate then enter the data as required and click OK, for the purpose of the blog post here is the data I entered:
  • image
  • On my PWA instance, GBP is the default currency used for this demo / blog post so this is set to 1.00 then I have a example currencies / rates for Euros. The project data in my PWA instance ranges from 2016 to 2018 so I need rates to cover those years
  • Click Add Column > Custom and enter the name “Year” with the formula of  Column Date.Year([Date]) and click OK
  • Right click on Master column and change the type to True / False
  • Change the table name to CurrencyData

The currency table is now completed.

Now we need to get the Task Timephased data, still within the Query Editor opened from creating the currency table table, see these steps:

  • New Source > OData Feed and add the OData URL for your PWA site: <PWASite>/_api/ProjectData/TaskTimephasedDataSet and click OK then OK again
  • Click Choose Columns and uncheck Select All to deselect all the columns then select at least ProjectId, TaskCost, TaskIsProjectSummary and TimeByDay and click OK
  • Change the table from Query2 to TaskData
  • Click the dropdown menu on the TaskCost column, if it states “List may be incomplete” click load more and then uncheck 0. In the advanced editor check the filter is ([TaskCost] <> 0)
  • Click the dropdown menu on the TaskIsProjectSummary column and uncheck false
  • Click Add Column > Custom Column and enter the name “Year” with the formula of Date.Year([TimeByDay]) and click OK
  • Click Merge Queries > Merge Queries, in the Merge window select Projects then select ProjectId in the TaskData table and ProjectId in Projects table:
  • image
  • Click OK
  • In the New Column column heading, click the Expand button, select just ProjectCurrency and uncheck the use original column name option:
  • image
  • Click OK
  • Click Merge Queries > Merge Queries, in the Merge window select CurrencyData then  hold the Ctrl key down and click Year and then ProjectCurrency in the TaskData table and then Year and then Currency in the CurrencyData table like below:
  • image
  • Click OK
  • In the New Column column heading, click the Expand button, select just Rate and uncheck the use original column name option:
  • image
  • Click OK
  • Click Add Custom > Custom Column and enter the name “TaskCost_Converted” with the formula of [TaskCost] * [Rate] and click OK
  • Right Click the column heading for TaskCost_Converted column and click Change Type > Decimal Number:
  • image 

The TaskData table is now complete. Click Close & Apply > Close & Apply. Check the table relationships are correct, it should just be Projects linked to TaskData using ProjectId.

Now design the report as required. For the purpose of this blog post I created one table with the following fields:

image

Ensure TaskCost and TaskCost_Converted fields are set to Sum and all other fields on the table are set to Don’t summarize. If you need to work with multiple currencies in reports, try this out and extend it for your specific needs.

#ProjectOnline #PowerBI Currency Conversion Project Cost Report Part 1 #PPM #BI #Office365

March 6, 2017 at 7:52 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , ,

Whilst Microsoft’s Office 365 PPM tool Project Online supports projects using different currencies, there is no conversion based on a currency rate. The project has a currency set and the correct currency symbol is displayed for cost data. In the reports and views there is no conversion based on an exchange rate between currencies. This blog post shows a simple way using Power BI to have a report calculate the project cost based on the defined currency as seen below:

image

In the steps below we walkthrough how to set up this simple example. Firstly in the Power BI Desktop client add the Projects OData feed:

  • Click Get Data > OData Feed and add the Odata URL for your PWA site: <PWASite>/_api/ProjectData/Projects and click OK
  • Click Edit to launch the Power BI Query Editor then click Choose Columns and uncheck Select All to deselect all the columns then select at least ProjectName, ProjectCost and  ProjectType and click OK
  • Click the dropdown menu on the ProjectType column and uncheck 7.
  • Change the table from Query1 to Projects

The Projects table is now completed.

Now we need to create a table for the different currencies and rates we want to use.

  • Click Enter Data to launch the Create Table window and create the columns “Currency” and “Rate”. Enter the data as needed and call the table Currency then click OK. I created the following:
  • image
  • On my PWA instance, GBP is the currency used for this demo / blog post so this is set to 1.00 then I have a example currencies / rates for Euros and USD

The Currency table is now completed. Click Close & Apply > Close & Apply. Create a table with the project data on then a slicer for the currency values from the currency table like below:

image

Now right click on the Projects table in the fields pane and click New measure:

image

Enter the following in the formula / query bar:

image

This gets the selected currency from our slicer as a value in the Projects table. This is then used in the next new measure we create. Right click on the Projects table in the fields pane and click New measure again:

image

Enter the following in the formula / query bar:

image

Now add the ProjectCost_Converted field into the table with the project data and change the slicer selection and notice the ProjectCost_Converted values change, as seen below for Euros (EUR):

image

For Pounds (GBP):

image

For US Dollars (USD):

image

This simple version doesn’t take into account for projects that span multiple years where different rates will apply, it could be extended to support that though. In part 2 later this week we look at pre-calculating the project cost so that the portfolio cost is correct based on the currency defined by the project, look out for that towards the end of the week.

Security trim Project data in #ProjectOnline #PowerBI #Excel #PowerQuery reports part 2 #PPM #Odata #REST #BI

December 12, 2016 at 5:46 pm | Posted in Administration, Functionality, Add-on, Customisation, Information, Configuration, Reporting | 1 Comment
Tags: , , , , , , ,

Further to my last post on an example method to security trim the Project data in a Power BI report for Microsoft’s Project Online PPM tool, another option is to merge the datasets / tables. If you didn’t see the first post, a link can be found below:

https://pwmather.wordpress.com/2016/12/09/security-trim-project-data-in-projectonline-powerbi-reports-ppm-odata-rest-bi/

For this post, I have the same two ODATA / REST queries used, the ODATA one for the Milestones (/_api/ProjectData/Tasks()?$Filter=TaskIsMilestone eq true) which is not security trimmed and the REST one for the Projects (/_api/ProjectServer/Projects) which is security trimmed. With both of these datasets loaded into the Power BI Query Editor, select the Milestone dataset and click Merge from the Home ribbon tab and set the following:

image

This is merging my two tables based on ProjectId from the Milestone dataset and Id from the Projects dataset using a Left Outer join. You can see from that screen shot that I don’t actually have access to the Agile Product Development project in PWA as it is not returned in the list of projects in the REST dataset. Clicking OK will show a NewColumn:

image

Click expand and select Name:

image

That returns nulls for the project names you do not have access to:

image

These rows should be filtered out, click the Name column heading dropdown and de-select null:

image

Click OK and the Milestone dataset table will match the list of projects you have access to in PWA – the rows with nulls in the Name column will be removed from the table. I then renamed the Milestones – ODATA dataset to Filtered Milestones:

image

Click Close & Apply then the load a couple of visualisations on to the report and you can see that now the data is security trimmed as part of the query and not in the report:

image

In this simple example there is no need for the report level filter to remove the nulls at the report level like there was in the previous post. The same applies as with the previous post, if your users know Power BI  / Power Query they can easily undo all of this and access all the data but this might be an option to explore.

Security trim Project data in #ProjectOnline #PowerBI reports #PPM #Odata #REST #BI

December 9, 2016 at 1:29 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting | 5 Comments
Tags: , , , , , , ,

The reporting API for Microsoft’s PPM solution – Project Online doesn’t filter the data based on the access model in PWA. For some organisations this can be problematic. In this blog post I show a simple report example to filter only projects and project milestones that I have access to in PWA.

In this example I have a very simple Power BI Report that is only using ODATA (_api/ProjectData), the Projects resource path to populate the Projects slicer / filter and the Tasks resource path to populate the Milestones for the selected Project:

image

I have access to a long list of projects (see the scroll bar on the slicer). Using the Audit Tracking project as an example, I can currently see this project in PWA as shown below:

image

I will change the access model so I get access denied to this project:

image

I only see these 6 projects projects in an unfiltered Project Center view:

image

Yet if I refresh my Power BI report I still see the full list as expected (the ODATA reporting API is not data security trimmed, you either access all data or none). One option is to use the REST (_api/ProjectServer) to filter the datasets that use the ODATA API as the source. The REST API is security trimmed to only show the data you have access to based on the access model in PWA. For example, I will get the list of Projects from REST to populate the Projects slicer / filter in Power BI, this only shows the projects I have access to in PWA. I have added a new data source to my simple Power BI report for the REST API:

image

The join is then set up on Id from the Projects REST dataset to the ProjectId in the Milestones OData dataset:

image

I now switch my slicer / filter to use the Projects – REST dataset

image

As you can see the list of projects matches what I see in the Project Center but there is a also a blank option there. The blank value is for rows it can’t join with on the Milestone ODATA dataset, these are the projects I no longer have permission to access in PWA! To resolve this, select the page and add a report level filter like below where Name is not blank:

image

This report filter will not show any blank values for the Name field on any visualisation in the report:

image

The blank value is now removed. My data is now security trimmed to match what I have access to in PWA! Obviously if you know what you are doing you can just undo all of this and see all data but for some it might be a workable solution! You can do the same with Resources too.

#ProjectOnline new #OData resource path for Resource Demand #Reporting #BI #O365 #PPM

November 22, 2016 at 9:35 am | Posted in Administration, Configuration, Functionality, Information, Reporting | Leave a comment
Tags: , , , , , ,

This post covers a new resource path in the OData endpoint for Microsoft’s PPM tool Project Online to give you the data you need for true resource demand for project assignments and resource engagement assignments. Before resource engagements were introduced, you could have assignments in the project plans or in the resource plans. For reporting, the data was available in the Assignments and the AssignmentTimephasedDataSet resource path feeds, normal project assignments data would be in the AssignmentWork field and resource plan work was in the AssignmentResourcePlanWork field. There was also an AssignmentCombinedWork in the AssignmentTimephasedDataSet feed that displayed the correct resource demand for the resource when you used both resource plans and project plans. The AssignmentCombinedWork field would take into account for the project resource utilisation calculation options. With the introduction of resource engagements there wasn’t a single field that gave you this same resource demand data – until now!

If you regularly view the Project OData service root URL (_api/ProjectData) you might have noticed a new feed, ResourceDemandTimephasedDataSet. This feed has the following properties:

image

The ResourcePlanUtilizationType type will display either a 0, 1 or 2. This is the property to indicate the project resource utilisation calculation options for the project. The enumeration for these values is below:

  • o = Project Plan
  • 1 = Resource Engagements
  • 2 = Project Plan Until

The ResourcePlanUtilizationDate property will only contain a date when the “Project Plan Until” resource utilisation calculation option is set.

This new ResourceDemandTimephasedDataSet feed will show the true resource demand for the project resource utilisation calculation option when using both project assignments and resource engagements. Some scenarios below show when data would and wouldn’t be included in this new feed:

Scenario 1: Project plan has resource assignments and resource engagements. The “Calculate Resource Utilization from:” option is set to Project Plan. In this scenario only the project assignments will be included in the ResourceDemandTimephasedDataSet feed.

Scenario 2: Project plan has resource assignments and resource engagements. The “Calculate Resource Utilization from:” option is set to Resource Engagements. In this scenario only the resource engagement assignments will be included in the ResourceDemandTimephasedDataSet feed.

Scenario 3: Project plan has resource assignments and resource engagements. The project assignments start from the 20th November to the 10th December 2016. The Resource engagements start from the 25th November to the 20th December 2016. The “Calculate Resource Utilization from:” option is set to Project Plan until with a date set to 1st December 2016. In this scenario only the project assignments will be included from the 20th November 2016 up to and including the 1st December 2016 and only resource engagement assignments from the 2nd December to 20th December 2016  will be included in the ResourceDemandTimephasedDataSet feed.

Example data can be seen below:

image

That makes things far easier for reporting resource demand, without this new feed you had to use the AssignmentTimephasedDataSet feed and the EngagementsTimephasedDataSet feed and join the datasets.

#ProjectOnline #OData reporting API updated to remove #HTML tags #Office365 #BI #Excel #PowerBI

May 30, 2016 at 6:36 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | Leave a comment
Tags: , , , ,

Recently you might have noticed that the Project Online OData reporting API has been updated to remove the HTML tags, I tweeted about this last week. So before this changed the data would have look like this:

image

Notice the HTML tags such as   and </li></ul></ul></ul> in the StatusSummary field. In the PDP the data looks like this in the Status Summary multiline text field:

image

If you are still seeing HTML tags for projects these will be updated once edited and saved, After making a change to a project custom field and clicking Save on a PDP (this does a Project Summary Publish too) for this example project I then see the following in the OData feed:

image

As you can see the HTML tags are now removed from the StatusSummary field. So now in your Excel or Power BI reports you will no longer have to either use VBA in Excel to remove these or use a similar Power Query function in Excel or Power BI as detailed here.

The only down side to this change is if you use a report that can render the HTML tags to maintain the formatting set in the multiline project level custom fields on the PDP this will be lost. For example, for PS+ we use a Reporting add-in that maintains the multiline custom field formatting as seen below:

image

After the update this formatting is lost as expected making the data harder to read:

image

All is not lost though, if you want to maintain this formatting just using CSOM / JSOM or REST to get the data for the multiline project custom fields. In the example below using the REST (/_api/ProjectServer) API you can see that the HTML tags are still available:

image

Here the fields are referenced using the Internal Name rather than the Name, for example Custom_x005f_4d0daaaba6ade21193f900155d153dd4. So you will need to update any custom add-ins / reporting tools to get the multiline custom field data from these API’s if you wish to maintain the formatting.

Want to query cross #project site #SharePoint lists in #ProjectOnline / #ProjectServer ? #PowerBI #PowerQuery #BI #Office365 #Excel #PPM

January 5, 2016 at 3:00 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , , ,

As you may know or not know, creating a report that contains data from a custom SharePoint list from all Project sites, for example a Lesson Learned list or Change list is not that simple. The default lists for Issues and Risks are simple as the data is synchronised to the reporting schema and available in the Project OData reporting API. In this blog post I will show you a simple example using Power Query to access the list data and create a simple cross project report consuming data from the SharePoint list APIs on the Project Sites.

In this example I use the Issues list but the same principle will apply to any SharePoint lists on the project sites. I have used Power BI Desktop to author this report but the same would work in Excel using Power Query. First load Power BI Desktop and click Get Data >  ODATA Feed and enter the REST API for the list using an example Project Site:

image

The URL I used was: https://TenantURL/sites/PSP/Acquisition%20Target%20Analysis/_api/web/lists/GetByTitle(‘Issues&#8217;)/Items()?$Select=Title,Priority

In this example I am only getting the item title and priority from the Issues list. To use a different list update GetByTitle(‘Issues’) with the name of your list and then update the Select to include the correct columns.

Click OK and you will see the data from this list:

image

Click Edit to load the query editor.

image

Rename the query to something meaningful;

image

Access the Advanced Editor:

image

Change the Power Query code to add in the following code to turn this into a parameterised function:

image

The top and bottom lines were added and the OData.Feed URL updated the remove the reference to the example site we used to load the initial data, this was replaced with the site parameter. Click Done and you will see the following:

image

Don’t invoke the function as this will edit the code. Now we need to add in the query to get the list of projects. So within the query editor on the Home tab click New Source > ODATA Feed and enter the URL to get you list of Projects and Project Site URLS:

image

The URL I used was: https://TenantURL/sites/PSP/_api/ProjectData/Projects()?$Filter=ProjectType ne 7

This filters out the Timesheet row, the properties will be selected in the next step. Click OK to load the data:

image

Click OK and click Choose Columns:

image

I only selected ProjectName and ProjectWorkspaceInternalUrl, other columns can be added but only these were required for this example:

image

Click OK and rename the Query to something meaningful:

image

We now need to add in a custom column to call the function. Click Add Column > Add Custom Column and enter the following:

image

So above we are calling the LoadIssueData function and passing in the ProjectWorkspaceInternalUrl. This will be used in the site parameter value. Click OK and the data will load:

image

The column needs to be expanded to show the data, click the expand button next to the column name and chose the columns to expand:

image

Click OK to load the data and expand the columns to see Title and Priority columns from the Issues list:

image

Now click Close & Apply from the Home tab to load the data model:

image

We can now create a report that displays the data, a simple table example can be seen below:

image

The expanded column can be renamed to something more meaningful, for example:

image

Depending on the number of projects / project sites the data load might take a while as it has to traverse all of the project sites and get the SharePoint data using the list REST APIs. Each Project requires a separate REST call to get the list data from the associated Project Site.

Hopefully that will open up your reporting options for getting data from those custom SharePoint lists or the Project sites or even if you want to use the default Issues and Risks list but have custom columns on those lists.

#ProjectOnline #PowerBI content pack available #BI #Office365 #PPM

November 18, 2015 at 9:43 am | Posted in Add-on, Administration, Functionality, Information, Reporting | 3 Comments
Tags: , , , , ,

The Project Online Power BI content pack is now available – take a look today!

Log in to your Power BI site, click Get Data then click Get on the Services card under the Content Pack Library:

image

Scroll down the list and you will see Microsoft Project Online:

image

Click the tile:

image

Then click Connect and type the PWA URL – I connected to one of our demo instances:

image

Click Next and change the Authentication method to oAuth2 and click Sign in:

image

When prompted, enter the credentials.

The content pack will then be deployed to your workspace, it will first import the data as displayed in the top right hand corner:

image

Once completed you will see the Microsoft Project Dataset, Report and Dashboard accessible in the left navigation pane:

image

There are default reports for Issues:

image

Risks:

image

Portfolio Status:

image

Project Compliance:

image

Then there is a dashboard that displays some of this data:

image image

Awesome work from the Project team at Microsoft 🙂

#ProjectVConf session summary for #ProjectOnline #BI #PowerBI #Excel #SSRS #JavaScript #SSIS

November 3, 2015 at 3:13 am | Posted in Administration, Information, Personal | 1 Comment
Tags: , , , , , ,

It’s been a few weeks now since the Project Virtual Conference, for those of you that missed it, sign up here: http://projectvirtualconference.com/ and you can view the recordings.

This post will give a summary of my session on Project Online BI Made Easy:

http://event.projectvirtualconference.com/B002/

The aim of this session was to give an overview on some of the reporting technologies available to Project Online, this included:

  • Excel
  • Power BI
  • SSIS / SSRS
  • JavaScript / HTML

Firstly I demonstrated some example Excel dashboards I created for the session, screen shots of these can be seen below:

The project report below is from my Project Online report pack found here: https://gallery.technet.microsoft.com/Online-Reporting-Pack-431f075e

image

The report below is an example portfolio dashboard showing key metrics / data:

image

The report below is an example portfolio type report that can filter by programme:

image

I then demonstrated some example Power BI reports and dashboards that I created for the sessions:

The report below contains 4 pages, the first is another example portfolio type report:

image

The second page shows an example Treemap visualisation for the projects in the portfolio based on the % complete:

image

The third page shows an example project report:

image

The final page displays the drill down capability in Power BI, it shows the total cost per project initially:

image

Clicking a project drills into show the task cost for that project:

image

I also put together an example dashboard containing visualisations from the reports and natural language queries from the dashboard data:

image

image

The third technology demonstrated was SSIS / SSRS. The reporting technology was SSRS (SQL Server Reporting Services) but without SSIS (SQL Server Integration Services) an SSRS report would not be possible with Project Online data. Well that isn’t strictly true but you need to extract the data out of Project Online into another data source such as SQL – SSIS can do this but so could a .NET console application using CSOM for example.

Below is an example SSRS project status / highlight report that displays data from my Project Online PWA instance:

image

The fourth technology was JavaScript / HTML, I put together an simple project report that displayed project information – project level data, milestones / marked tasks, issues, risks and data from a custom list on my project site called benefits. This also demonstrated the capability of rendered the HTML tags in multiline fields and accessing custom SharePoint list data from the associated project site. The report can be seen below:

image

I also demonstrated a reporting add-in that we use for PS+ but this was just to give another example using JavaScript / HTML:

image

I then covered a bit around best practice when using the Odata API regarding filtering the data at source as much as possible to minimise the data being pulled down – this means using filters and selects to only pull the information you need.

The final part I walked through creating a new Excel based report from a blank workbook and also create a new Power BI report from a blank Power BI Desktop file.

Take a look at the session for more details.

To help get started with reporting in Project Online, take a look at some of the links below:

Excel Project Online Report Pack: https://gallery.technet.microsoft.com/projectserver/Online-Reporting-Pack-431f075e

https://pwmather.wordpress.com/2014/09/05/microsoft-projectonline-reporting-pack-msproject-sharepointonline-office365-ppm-excel-bi-data/

Blogs posts on Excel / Odata:

https://pwmather.wordpress.com/2013/09/16/projectserver-projectonline-example-report-ps2013-sp2013-office365-bi-excel-powerpivot/

https://pwmather.wordpress.com/2013/08/01/projectserver-projectonline-highlight-report-example-ps2013-sp2013-excel-bi-office/

https://pwmather.wordpress.com/2015/01/20/projectonline-excel-powerquery-report-office365-ps2013-bi-odata/

https://pwmather.wordpress.com/2015/04/13/projectonline-projectserver-reporting-on-auto-manually-scheduled-tasks-bi-office365-excel-powerquery-ps2013/

https://pwmather.wordpress.com/2015/10/09/removing-html-tags-from-projectonline-fields-powerquery-powerbi-excel/

https://pwmather.wordpress.com/2014/07/17/getting-started-with-projectonline-part-9-ps2013-office365-project-ppm-sharepointonline-pm-sp2013/

SSIS links:

https://pwmather.wordpress.com/2014/03/26/projectonline-data-via-odata-and-ssis-in-sql-database-table-on-premise-msproject-sharepointonline-bi-ssrs-office365-cloud/

https://msdn.microsoft.com/EN-US/library/office/dn794163.aspx

Power BI:

https://pwmather.wordpress.com/2015/08/04/projectonline-reporting-using-powerbi-part1-bi-office365-reports-ppm-pmot/

https://pwmather.wordpress.com/2015/08/07/projectonline-reporting-using-powerbi-part2-bi-office365-reports-ppm-pmot/

JavaScript examples:

https://pwmather.wordpress.com/2014/10/26/project-on-a-page-for-microsoft-projectonline-ps2013-javascript-jquery-office365-sharepoint/

https://pwmather.wordpress.com/2015/02/25/projectonline-projectserver-project-cost-and-work-report-javascript-jquery-office365-sharepoint-bi-ppm/

https://pwmather.wordpress.com/2015/05/01/projectonline-projectserver-view-resource-calendar-exceptions-javascript-jquery-office365-sharepoint/

There are plenty of details out there, just have a quick search and you will find lots of helpful articles for each technology!

Removing HTML tags from #ProjectOnline fields #PowerQuery #PowerBI #Excel

October 9, 2015 at 1:03 pm | Posted in Administration, Customisation, Functionality, Information, Reporting | 5 Comments
Tags: , , , ,

A quick post to highlight a simple way to remove the HTML tags in the Project Online OData reports. If you are unsure about what I mean, see the image below:

image

Notice the <p> tag and &#160 tag in the Changes column above, as well as the list tags. An easy way to clean this up is to use Replace function in Power Query:

image

This can be seen below too:

let
    Source = OData.Feed("<PWAURL>/_api/ProjectData/Projects()?$Filter=ProjectType ne 7"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"ProjectName", "Changes"}),
    #"Replace HTML <p>" = Table.ReplaceValue(#"Removed Other Columns","<p>","",Replacer.ReplaceText,{"Changes"}),
    #"Replaced HTML </p>" = Table.ReplaceValue(#"Replace HTML <p>","</p>","",Replacer.ReplaceText,{"Changes"}),
    #"Replaced HTML &#160" = Table.ReplaceValue(#"Replaced HTML </p>"," ","",Replacer.ReplaceText,{"Changes"}),
    #"Replaced HTML <ul><li>" = Table.ReplaceValue(#"Replaced HTML &#160","<ul><li>"," ",Replacer.ReplaceText,{"Changes"}),
    #"Replaced HTML </li><li>" = Table.ReplaceValue(#"Replaced HTML <ul><li>","</li><li>",", ",Replacer.ReplaceText,{"Changes"}),
    #"Replaced HTML </li></ul>" = Table.ReplaceValue(#"Replaced HTML </li><li>","</li></ul>"," ",Replacer.ReplaceText,{"Changes"})
in
    #"Replaced HTML </li></ul>"

Then your report will look like this in Excel:

image

This also works for Power BI too, without the replace function:

image

Using the replace function:

image

I have only included a few of the HTML tags / mark-up that you will find but as you can see, it will be easy enough to do the rest.

This is a quick and simple way but you could look to do this in bulk for all columns and HTML tags by creating your own function if you needed to.

Next Page »

Blog at WordPress.com.
Entries and comments feeds.