none
Setting country or city location of a project? RRS feed

  • Question

  • Hi all,

    This is a query based on reporting purposes.

    Is there a way to set or record anywhere the country or ideally city that a project is located? I assume there is a way because of the way multi-nationals as an example work and assume they would want to be able to quickly report on where they currently have projects running, but I haven't been able to figure it out. Power BI has a map ability, but it's setting the location on the map from the information it gets in the project title. Anyone else aware of another way to do this? Thanks.

    Wednesday, June 5, 2019 2:11 PM

Answers

  • Hi,

    Why don'y you use a custom field with the location? Then you'll be able to use it in PowerBI and map the project on a 3D map for example.

    See this article I wrote a while ago using project location in Power Map.

    https://psbehindthescene.blogspot.com/2015/08/my-first-power-map-tour.html


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller

    • Marked as answer by DangerM0use Wednesday, June 5, 2019 7:31 PM
    Wednesday, June 5, 2019 2:47 PM
    Moderator
  • But,,,, with the beauty of Power Query you can do this with. 

    I referenced the projects query, got rid of everything except project name and location, split the location column by a comma, removed the rows where location.1 = null, removed the leading space from the other location fields, and then unpivoted the columns based on the project name.

    The result is this resultinpbi report.


    Ben Howard [MVP] | web | blog | book | downloads | P2O

    Thursday, June 6, 2019 12:32 PM

All replies

  • Hi,

    Why don'y you use a custom field with the location? Then you'll be able to use it in PowerBI and map the project on a 3D map for example.

    See this article I wrote a while ago using project location in Power Map.

    https://psbehindthescene.blogspot.com/2015/08/my-first-power-map-tour.html


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller

    • Marked as answer by DangerM0use Wednesday, June 5, 2019 7:31 PM
    Wednesday, June 5, 2019 2:47 PM
    Moderator
  • Perfect, thanks.
    Wednesday, June 5, 2019 7:31 PM
  • Just to add to Guillaume's excellent answer, you might want to use City, Country (eg London, UK) as just using the city is often not enough, especially in an international setting.  To be truly accurate enter the Lat and Long for each project location, along with the city name.   In Power BI, set the data type for the lat and long fields to be lat and long, and add the city name into the tooltip.

    Ben Howard [MVP] | web | blog | book | downloads | P2O

    Wednesday, June 5, 2019 9:05 PM
  • Thanks for the tip. I have noticed one example where it thought an area was in America and not in the UK.

    The one thing I have really struggled with and not managed to get to work so far is when multiple locations are entered. So there are occasions where our projects are based or cover multiple areas. I have a custom field which allows multiple entries but Power BI doesn't seem to recognise this.

    Thursday, June 6, 2019 11:00 AM
  • Indeed it cannot be a multi-value field, since PowerBI (and Project behind) cannot allocated data (work, cost etc...) between the different values selected for the location if you have a multi value field.

    We suggest to keep it with a unique selection possible, and either add a description or a secondary field.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller

    Thursday, June 6, 2019 11:51 AM
    Moderator
  • But,,,, with the beauty of Power Query you can do this with. 

    I referenced the projects query, got rid of everything except project name and location, split the location column by a comma, removed the rows where location.1 = null, removed the leading space from the other location fields, and then unpivoted the columns based on the project name.

    The result is this resultinpbi report.


    Ben Howard [MVP] | web | blog | book | downloads | P2O

    Thursday, June 6, 2019 12:32 PM
  • Thanks Ben, useful!

    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller

    Thursday, June 6, 2019 12:57 PM
    Moderator
  • Oh that's perfect. Thanks very much.
    Thursday, June 6, 2019 2:19 PM