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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors