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

[MaoMiau]

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! 

[amitchandak · Accepted solution]

@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

Remove Na from City if needed

[MaoMiau]

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!