Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mannymann
Frequent Visitor

Build Heat Map using x and y coordinates

Hello All,

How can we plot or use x and y coordinates instead of longitude and latitude in the heat map? 

Even a formula to convert x and y into longitude and latitude. 

 

Please let me know if this is possible.

 

Thank you, everyone!! 

6 REPLIES 6
Fortunato_INB
Frequent Visitor


Step 1 (Create the role):
- Copy the code below.
- Go to Power Query in the "Home" tab and click "Enter Data" and click "OK".
- Click on the table that was just created, and click on "Advanced Editor".
- Paste the code and click "Done".

Step 2 (Invoke function):
- Select the table you want to transform the coordinates.
- In the "add column" tab, click "Invoke Custom Function".
- Select the function you created and inform the columns that are the coordinates UTM (north and east), Hemisphere (N for north and S for South), Central Meridian and click "OK".

Code (M language):
 

 

let
/*Fórmula para converter coordenadas UTM para Geográficas
Desenvolvido por: José Carlos Fortunato - fortunatojosecarlos@gmail.com*/
    UtmLatLong = (UTMeste as number,UTMnorte as number,Hemisferio as text,MeridianoCentral as number) =>
        let
        long0 = MeridianoCentral,
        Hem = Hemisferio,
        TextHem = Text.Upper(Hem),
        a = 6378137.0000,
        b = 6356752.3141,
        x = 500000 - UTMeste,
        y = UTMnorte,

        y1 = if TextHem = "S" or TextHem = "SOUTH" or TextHem = "SUL" then
        10000000 - y
        else y,

        M = y1 / 0.9996,
        e = Number.Sqrt(1 - Number.Power(b,2) / Number.Power(a,2)),
        mu = M / (a * (1 - Number.Power(e,2) / 4 - 3 * Number.Power(e,4) / 64 - 5 * Number.Power(e,6) / 256)),
        e1 = (1 - Number.Power((1 - Number.Power(e,2)),(1 / 2))) / (1 + Number.Power((1 - Number.Power(e,2)),(1 / 2))),

        j1 = (3 * e1 / 2 - 27 * Number.Power(e1,3) / 32),
        j2 = (21 * Number.Power(e1,2) / 16 - 55 * Number.Power(e1,4) / 32),
        j3 = (151 * Number.Power(e1,3) / 96),
        j4 = (1097 * Number.Power(e1,4) / 512),

        fp = mu + j1 * Number.Sin(2 * mu) + j2 * Number.Sin(4 * mu) + j3 * Number.Sin(6 * mu) + j4 * Number.Sin(8 * mu),

        e2 = Number.Power(e,2) / (1 - Number.Power(e,2)),
        c1 = e2 * Number.Power((Number.Cos(fp)),2),
        t1 = Number.Power(((Number.Sin(fp)) / (Number.Cos(fp))),2),
        r1 = a * (1 - Number.Power(e,2)) / Number.Power((1 - Number.Power(e,2) * Number.Power((Number.Sin(fp)),2)),(3 / 2)),
        n1 = a / Number.Power((1 - Number.Power(e,2) * Number.Power((Number.Sin(fp)),2)),(1 / 2)),
        d = x / (n1 * 0.9996),

        q1 = n1 * ((Number.Sin(fp)) / (Number.Cos(fp))) / r1,
        q2 = Number.Power(d,2) / 2,
        q3 = (5 + 3 * t1 + 10 * c1 - 4 * Number.Power(c1,2) - 9 * e2) * Number.Power(d,4) / 24,
        q4 = (61 + 90 * t1 + 298 * c1 + 45 * Number.Power(t1,2) - 3 * Number.Power(c1,2) - 252 * e2) * Number.Power(d,6) / 720,

        latrad = fp - q1 * (q2 - q3 + q4),

        lat = if TextHem = "S" or TextHem = "SOUTH" or TextHem = "SUL" then
            -1 * (latrad * (180 / Number.PI))
        else
            (latrad * (180 / Number.PI)),


        q5 = d,
        q6 = (1 + 2 * t1 + c1) * Number.Power(d,3) / 6,
        q7 = (5 - 2 * c1 + 28 * t1 - 3 * Number.Power(c1,2) + 8 * e2 + 24 * Number.Power(t1,2)) * Number.Power(d,5) / 120,

        longrad1 = (q5 - q6 + q7) / (Number.Cos(fp)),
        longrad2 = longrad1 * (180 / Number.PI),

        longi = if long0 < 0 then
            long0 - longrad2
        else
            long0 + longrad2

        in
        Table.FromRecords({[latitude = lat,longitude = longi]})
        
        //UtmLat = Table.AddColumn(UtmLatLong, "latitude", each List.First(UtmLatLong([este],[norte], "SUL", -45),1),type number),
        //UtmLong = Table.AddColumn(UtmLat, "longitude", each List.Last(UtmLatLong([este],[norte], "SUL", -45),1),type number)

in
UtmLatLong

 


Hope this helps!

@Fortunato_INB thank you for replying.  I followed the instruction and during Step 2, I didn't get any output back after Apply and close.  All we get is [Table] in our output row.

UMT.PNG

Whereas, we were looking for long and lag fields. 

 

However, I'm only able to transform just one row by manually entering the value before invoking the function (before STEP 2). We are looking for a solution when we refresh the data table the x and y coordinations are automatically transformed into long and lat.  

Would you be able to suggest a solution or correct me? 

 

Thank you 

v-eachen-msft
Community Support
Community Support

Hi @mannymann ,

 

You could contact with the author of Heat Map to add this feature. Here is the link:
https://weiweicui.github.io/PowerBI-Heatmap/

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
lbendlin
Super User
Super User

For small cardinalty of x and y:

 

- Use a matrix visual

- put your x data in the columns

- put your y data in the rows

- put your measure into the values

- now use conditional formatting based on the measure. Use the same condition for font color and background color.

 

Or use a scatter plot visual.

Hi, Is it possible to plot the x and y coordinates on the heat map instead of longitude and latitude coordinates?

For example,

x-coordinates :454014 y-coordinates :4944234 coordinates.PNG

 

Thank you

That won't help.

I am looking forward to plotting my coordinates on the Heat Map, not onto the scatter plot visual.

 

Thanks for responding.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors