Search code examples
powerbidaxpowerquerypowerbi-desktopcost-management

Power BI :: How to join 2 tables with 1 column to multiple columns

GOAL: I want to forecast if Azure Reserved Instances are the right choice for us.

HOW TO DO IT:

  1. I have downloaded the whole Azure Price REST API through this Python script.

  2. I have imported that CSV in an Azure SQL Database

  3. I want to compare each Reserved Instance with the resources we have on Azure thanks to the the Azure Cost Management connector in Power BI Desktop

THE PROBLEM: In a perfect world I would like to see every resources listed like this:

unitPrice 1 Year Reservation 3 Years Reservation
1.2671 6528.3905 12524.2148

But we don't live in a perfect world and the data are organized this way:

unitPrice meterId PK productName skuName location serviceName unitOfMeasure type armSkuName reservationTerm
6528.3905 003e1713-c374-4003-9a73-27b3ccc80c38 Virtual Machines Ev3 Series - E16 v3 - EU West Virtual Machines Ev3 Series E16 v3 EU West Virtual Machines 1 Hour Reservation Standard_E16_v3 1 Year
1.2671 003e1713-c374-4003-9a73-27b3ccc80c38 Virtual Machines Ev3 Series - E16 v3 - EU West Virtual Machines Ev3 Series E16 v3 EU West Virtual Machines 1 Hour Consumption Standard_E16_v3 NULL
12524.2148 003e1713-c374-4003-9a73-27b3ccc80c38 Virtual Machines Ev3 Series - E16 v3 - EU West Virtual Machines Ev3 Series E16 v3 EU West Virtual Machines 1 Hour Reservation Standard_E16_v3 3 Years

So I created a Primary Key based on the productName, skuName and Location.

I was at the phone with Microsoft and they confirmed that meterId is not a unique identifier.

enter image description here

THE QUESTION: Now that I have a unique identifier I can pivot the 1 Year and 3 Year to put everything on the same row.

tierMinimumUnits PK armRegionName location meterId meterName productId availabilityId productName skuName serviceName serviceId serviceFamily unitOfMeasure isPrimaryMeterRegion armSkuName effectiveEndDate RI_unitPrice RI_DevTestConsumption RI_1Year RI_3Years
0.0 Virtual Machines Ev3 Series - E16 v3 - EU West westeurope EU West 003e1713-c374-4003-9a73-27b3ccc80c38 E16 v3/E16s v3 DZH318Z0BQ4L NULL Virtual Machines Ev3 Series E16 v3 Virtual Machines DZH313Z7MMC8 Compute 1 Hour True Standard_E16_v3 NULL 1.2671 NULL 0.744739961213781 0.476242102060993

But I ask myself if I'm not doing this wrong.

If the data are on 3 separate rows maybe there is a way through Power Query to keep the data on 3 separate row and write a rule that says

"pick up 1 Year and 3 Years from 3 rows having a uniue identifier"

What is the best approach?

Dataset available on request.

EDIT:

Here are the raw data, I want to target the Virtual Machine D4 v3:

The column meterId is misleading: it is not a primary key. I also called Microsoft and they confirmed it is not a primary key.

As result I would like to have on the same line: ProductName, effectivePrice, 1Year_unitPrice (need to be pivoted?), 3Years_unitPrice (need to be pivoted?).

I know how to pivot this is SQL. I'm just asking myself if I'm not doing it wrong. Maybe there is a better way to do this in Power BI and I will have less work on the ETL process.

Thank you

Solution

  • It is a bit difficult to understand what your goal is but I think you want the following?

    enter image description here

    If so, just import your tables into PBI and leave them with no relationship.

    enter image description here

    Create the following 3 measures.

    Effective Price = 

VAR productName = SELECTEDVALUE('Azure Price List'[productName])
VAR skuName = SELECTEDVALUE('Azure Price List'[skuName])
VAR location = SELECTEDVALUE('Azure Cost Management'[location])

VAR tempKey =   productName + " - " + skuName + " - " + location

VAR result = CALCULATE(MIN('Azure Cost Management'[effectivePrice]), TREATAS({tempKey}, 'Azure Price List'[productName]))

RETURN result


1 Year Price = 
CALCULATE(MIN('Azure Price List'[unitPrice]), 'Azure Price List'[reservationTerm] = "1 Year")


3 Year Price = 
CALCULATE(MIN('Azure Price List'[unitPrice]), 'Azure Price List'[reservationTerm] = "3 Years")

    Add everything to a table:

    enter image description here