How to create a column with a specific row value.

[Jag_G12345]

Hi, I'm in a bind with power bi desktop. Currently the data is like this: 

I'm trying to create a visual that shows the score of each area. Like this: 

My issue is that the area is also listed in the rows, i'm trying to extract it to create a new column not sure how to proceed. 

[Anonymous · Accepted solution]

@Jag_G12345 Attached pbix file FYR.
Few Power query transformation required to prepare the data in the tabular model.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUXIsSk0EUUqxOtFKQEZxcn5RqiGQYWiJImQEEjICCxmbmBqZIbQ6YWo1wdRqDhYyNzM3NUVodcbUaoyp1VApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Audit ID" = _t, Category = _t, Response = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Audit ID", Int64.Type}, {"Category", type text}, {"Response", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Audit ID"}),
    #"Added Conditional Column" = Table.AddColumn(#"Filled Down", "Custom", each if Text.StartsWith([Category], "Area") then "Category" else "Response"),
    #"Pivoted Column" = Table.Pivot(#"Added Conditional Column", List.Distinct(#"Added Conditional Column"[Category]), "Category", "Response"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}),
    #"Filled Down1" = Table.FillDown(#"Removed Columns",{"Area"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each ([score1] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"score1", Int64.Type}, {"score2", Int64.Type}})
in
    #"Changed Type1"

If this post helps, then please consider Accept it as the solution
Thanks,
Kumar

[amitchandak]

@Jag_G12345 , In power query in Transform data -> split column by digit and non digit and create two columns

Split Column By Digit to Non Digit & Non Digit to Digit: https://youtu.be/tY4Yk1crS9s

Then use fill down -Fill Up Fill Down: https://youtu.be/mC2ps0pFqBI

then filter the rows with null response (number column)

and then use it