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:

I have downloaded the whole Azure Price REST API through this Python script.
I have imported that CSV in an Azure SQL Database
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

It may be just me, but I can't picture what the before data looks like, and the after data you are trying to get to. Maybe you can post a sample of before/after — horseyride, Nov 30 '22 at 17:53
Hi @horseyride and thank you so much for your help. I have added the raw data that you can now download. Let me know how you would solve this problem. I'm currently concatenating the 3 columns in SQL so there is a match 1:1 between the 2 files. But maybe you have a better idea about how to do that in Power BI. And I'm also pivoting in SQL but maybe there is a better way to do that in Power BI — Francesco Mantovani, Dec 05 '22 at 15:26
Sorry, I still don't understand the issue. What is wrong with the answer given below? — horseyride, Dec 05 '22 at 18:14
@horseyride how to link 3 column from one table to 1 column on another table in the data model: https://snipboard.io/nD6Sl0.jpg — Francesco Mantovani, Dec 05 '22 at 19:50
@horseyride, I don't get how he pivoted the 1 year and 3 years onto the same row — Francesco Mantovani, Dec 06 '22 at 10:43

score 1 · Answer 1 · answered Dec 01 '22 at 01:44

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"

Hi @Marc. Thank you so much, you did a massive job. I have uploaded the 2 files with the raw data and I put a bounty. Do you think there is a better way to link the 3 columns from the first file to a single column to the second file? Do you think there is a way to pivot data in Power BI? — Francesco Mantovani, Dec 05 '22 at 15:27
Hi @Marc, can you tell me how you pivoted the 1 Year and 3 Years in Power BI? — Francesco Mantovani, Dec 06 '22 at 10:42
I didn't pivot them in Power BI. I did the pivoting with Power Query. The steps called "Added Custom" and "Expanded Custom" in my M code basically do the pivot. "Added Custom" gets a record from the table that I had created by grouping and which I then transformed (as described in my answer). And then "Expanded Custom" extracts the information into new columns. Regardless... It looks like @David Bacci provided a better answer for your needs. — Marc Pincince, Dec 07 '22 at 22:00
yes, but I also liked your reply and I learned form it. Thank you so much for the time you spent helping me — Francesco Mantovani, Dec 08 '22 at 07:53

score 1 · Accepted Answer · answered Dec 06 '22 at 11:53

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:

I express myself very badly but this is exactly what I wanted. I'm going to test it right now and I will let you know — Francesco Mantovani, Dec 06 '22 at 12:04

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

2 Answers2