Forum Discussion

New2PowerBI's avatar
New2PowerBI
Helper III
9 years ago

Obstacle in Creating Saturation in Maps

I would like to create a saturation map where all scheduled work can be displayed (the saturation of color would be based upon the count of the work against any given GPS point), however, the data I have does not contain lat long information. 

 

I do have a "key" I can bring in from another source.  For example, my "key" value is "PS.ICT", and it has the lat long information. 

 

My problem comes with my main data set, my Preventative Work Table, it contains more than just "PS.ICT", it contains names, such as "PS.ICT.MTR.01"...I know that everything with "PS.ICT" is at that location, but how do I get the data to do that?  I think once I figure that piece out I can connect my external "key" with my Preventative Work Table and that should lay the foundation for an easy map build. 

 

Thank you in advance for your help!  :-) 

Need Help

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    New2PowerBI

    If the other "keys" are in the same pattern, I mean the leading part before the second dot of the field can be extracted as the key, you can try 

     

    let
    Source = Table.FromRows({{"PS.ICT.MTR.01"},{"PS.ICT.MTR.02"},{"PS.ICT.MTR.03"},{"A.BC.XXXXXX"},{"AB.CD.XXXX"},{"ABC.D.XXX"}},{"key"}),
    PositionOf1stDot = Table.AddColumn(Source, "1stDotPos", each Text.PositionOf([key],".",Occurrence.First)),
    PositionOf2ndDot = Table.AddColumn(PositionOf1stDot , "2ndDotPos", each Text.PositionOf(Text.Range([key],[1stDotPos]+1),".",Occurrence.First)+[1stDotPos]),
    actualKey = Table.AddColumn(PositionOf2ndDot,"actualKey", each Text.Range([key],0,[2ndDotPos]+1)) 
in
    actualKey

    • New2PowerBI's avatar
      New2PowerBI
      Helper III
      9 years ago

      I've not worked with anything like this before; can you provide additional detail on the steps? 

       

      Also got me to think, if I just "strip" the values to include only text, first dot, and then text before decond dot, do I even need a "key"...at that point, I can relate the new, stripped values, to the other table I have with GPS units to make that map work.  AM I way off here?