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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jag_G12345
New Member

How to create a column with a specific row value.

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

 

Jag_G12345_0-1648046363449.png

 

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

 

Jag_G12345_1-1648046697404.png

 

 

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. 

1 ACCEPTED SOLUTION
kumarthombre20
Resolver I
Resolver I

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

kumarthombre20_0-1648054793223.png

 

 

 

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

View solution in original post

2 REPLIES 2
kumarthombre20
Resolver I
Resolver I

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

kumarthombre20_0-1648054793223.png

 

 

 

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
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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