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

Question

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.

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:

• Azure Price List - D4 v3.xlsx : here are the 3 columns productName, skuName, location
• Azure Cost Management - D4 v3.xlsx : the 3 previous column need to converge here into ProductName

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

Answer 1

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

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

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:

Answer 2

This seems to work for me.

For this solution, I started from a table in excel. You can change your source appropriate to you. I set up my table in excel to look like your table example, using your data, but then I also added some additional dummy rows.

You may notice I moved the order of the occurrences of the reservationTerms around...not repeating the same pattern for them. I did this because I am not sure if yours will have a pattern, and I use the reservationTerms to name columns later. By moving them around, I made sure my solution would not depend upon them being in any certain order--it should accommodate any order of appearance.

I brought the excel table into Power Query as Table1. I then grouped by productName, skuName, and location, selecting all rows. After that, I did some transformation work within the tables embedded in each group's row. Then I added a column to extract a record with the unit prices from each embedded table in each row. Then I expanded the records. Lastly, I removed the column with the tables that I had created by grouping. The result looks like this.

Here's the M code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"unitPrice", type number}, {"meterId", type text}, {"PK", type text}, {"productName", type text}, {"skuName", type text}, {"location", type text}, {"serviceName", type text}, {"unitOfMeasure", type text}, {"type", type text}, {"armSkuName", type text}, {"reservationTerm", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"productName", "skuName", "location"}, {{"AllData", each _, type table [unitPrice=nullable number, meterId=nullable text, PK=nullable text, productName=nullable text, skuName=nullable text, location=nullable text, serviceName=nullable text, unitOfMeasure=nullable text, type=nullable text, armSkuName=nullable text, reservationTerm=nullable text]}}),
    #"Demoted Headers" = Table.TransformColumns(#"Grouped Rows", {"AllData", each Table.DemoteHeaders(_)}),
    Custom1 = Table.TransformColumns(#"Demoted Headers", {"AllData", each Table.Transpose(_)}),
    Custom4 = Table.TransformColumns(Custom1, {"AllData", each Table.RenameColumns(_,List.Zip({Table.ColumnNames(_),
Record.ToList(Table.SelectRows(_, each [Column1] = "reservationTerm"){0})}))}),
    #"Added Custom" = Table.AddColumn(Custom4, "Custom", each [AllData]{[reservationTerm="unitPrice"]}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"1 Year", "NULL", "3 Years"}, {"1 Year", "NULL", "3 Years"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AllData"})
in
    #"Removed Columns"