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.
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!
Solved! Go to Solution.
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.
Nathaniel
et 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"
Proud to be a Super User!
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.
Nathaniel
et 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"
Proud to be a Super User!
Thanks Nathaniel - that did the trick!
Sweet!
Proud to be a 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |