Forum Discussion
Extract Different Slicers from a Column
I have a table of salary information for the US. There is a lot of information in this table but 2 of the notable columns are labor category and Geography. It is the Geography column where I am running into my hurdle. This column contains Metro Area or State or Region (i.e. "Abilene,TX", "Florida (State)", or "West (Region)". I want to turn this column into 3 different slicers. One slicer would contain Metro Areas, the second States and the third Regions. How do I accomplish this? Do I create a new table using only this column and parse out the selections appropriately or do I parse out the 3 different values into different columns in the original table and then create a table with the DISTINCT values from the newly created columns in the original table? I spent some time trying to parse out the different values and was having a tough time finding a way to do it in a few steps as opposed with replaces row by row. Any help would greatly be appreciated!
One way is to add three columns with IF in Power Query see code and pics. And as you said you could use Distinct
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathanielet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckzKzEnNS9UJqVCK1YlWcsvJL8pMSVTQCC5JLEnVBIuFpxaXKGgEpaZn5ucBRWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Geography = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Geography", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "State", each if Text.Contains([Geography], "(State)") then [Geography] else ""), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Region", each if Text.Contains([Geography], "(Region)") then [Geography] else ""), #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Metro", each if [State] = "" and [Region] = "" then [Geography] else ""), #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column2",{{"State", type text}, {"Region", type text}, {"Metro", type text}}) in #"Changed Type1"
4 Replies
- Nathaniel_C
Community Champion
One way is to add three columns with IF in Power Query see code and pics. And as you said you could use Distinct
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathanielet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckzKzEnNS9UJqVCK1YlWcsvJL8pMSVTQCC5JLEnVBIuFpxaXKGgEpaZn5ucBRWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Geography = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Geography", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "State", each if Text.Contains([Geography], "(State)") then [Geography] else ""), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Region", each if Text.Contains([Geography], "(Region)") then [Geography] else ""), #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Metro", each if [State] = "" and [Region] = "" then [Geography] else ""), #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column2",{{"State", type text}, {"Region", type text}, {"Metro", type text}}) in #"Changed Type1"Thanks Nathaniel - that did the trick!
- Nathaniel_C
Sweet!
- amitchandak
Super User
There is a good article around operations in power bI for data cleaning
https://www.digitalvidya.com/blog/data-cleaning-features-in-power-bi/
One way is you clean in Power BI in three new columns.
Also, you can explore download this field data. Create a visual and download unique rows. Then clean it outside. Load it back as a table. In case these values do not change fast you can have this a separate table. If these changes fast then merge these back with the original table.
Just my thoughts.
