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
MaoMiau
Regular Visitor

How to deal with the situation, when a primary key has multiple values of a column?

I am a Power BI beginner and I want to create a dashboard to track my projects. There is a problem of my data which prevents me from building useful relationships between the tables.

 

My raw data look kind of like this:

 

ProjectKey

City 1

City 2

City 3

Technology 1

Technology 2

Total Investment

001

New York

NA

NA

A1

B1

10000

002

Los Angeles

Paris

London

C1

NA

20000

003

San Francisco

Berlin

NA

A1

D1

50000

 

Although the ProjectKey is unique, it has several entries under the categories City and Technology. How should I model my data, so that I can keep all the aspects in my raw data to create meaningful relationships? 😞

 

I would be grateful for any advice! Thank you so much! 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@MaoMiau , Try this code in Power query-> Blank Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLLVeIzC/KBjEdYYQjSMYJRBgaAIFSrE60khGQ55NfrOCYl56ak1oM5AUkFmUWg0XzUvLzgAxnQ5gBRnBtxkBecGKegltRYl5yZnFyPsjk1KKczDwUy1xAhClEVywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectKey = _t, #"City 1" = _t, #"City 2" = _t, #"City 3" = _t, #"Technology 1" = _t, #"Technology 2" = _t, #"Total Investment" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectKey", Int64.Type}, {"City 1", type text}, {"City 2", type text}, {"City 3", type text}, {"Technology 1", type text}, {"Technology 2", type text}, {"Total Investment", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ProjectKey", "Total Investment"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value", List.Max)
in
    #"Pivoted Column"

 

You will get data like

amitchandak_0-1702267807113.png

 

Remove Na from City if needed

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@MaoMiau , Try this code in Power query-> Blank Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLLVeIzC/KBjEdYYQjSMYJRBgaAIFSrE60khGQ55NfrOCYl56ak1oM5AUkFmUWg0XzUvLzgAxnQ5gBRnBtxkBecGKegltRYl5yZnFyPsjk1KKczDwUy1xAhClEVywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectKey = _t, #"City 1" = _t, #"City 2" = _t, #"City 3" = _t, #"Technology 1" = _t, #"Technology 2" = _t, #"Total Investment" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectKey", Int64.Type}, {"City 1", type text}, {"City 2", type text}, {"City 3", type text}, {"Technology 1", type text}, {"Technology 2", type text}, {"Total Investment", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ProjectKey", "Total Investment"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value", List.Max)
in
    #"Pivoted Column"

 

You will get data like

amitchandak_0-1702267807113.png

 

Remove Na from City if needed

Hi @amitchandak, thank you so much for your reply! I actually also pivoted the table into almost like what you show me here, but only per hand XD. I see that you kept the investment sum repeated for City 2 & 3. Wouldn't it make the ALL Investment higher? This was my second confuse after pivoting the table. What should I do with the blank cells, should I repeat all the values for City 2 & 3 or leave them blank except the project key?

 

And also, I see there is an Attribute.2 column in your table. Do I also need that? 

 

Sorry for the many further questions, and thanks very much in advance! 

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.