Forum Discussion
create table with unique values from another table columns
Hello Everyone!
I am pretty new to Power BI so any help is highly appreciated. I have a table (called survey) with answers IDs and the corresponding answers. If the person surveyed worked in one of the areas the answer is not null. So there are rows with more than one area filled up:
| Answer ID | Sales | Marketing | Finance |
| 1 | Sales | null | Finance |
| 2 | null | Marketing | null |
| 3 | Sales | Marketing | Finance |
What I would need to create is a table from Survey table as follows:
| Answer ID | Area |
| 1 | Sales |
| 1 | Finance |
| 2 | Marketing |
| 3 | Sales |
| 3 | Finance |
| 3 | Marketing |
Thank you so much!!
Hi ibarraza ,
Try this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzEktBtJ5pTk5QMotMy8xLzlVKVYnWskIIeybWJSdWpKZlw4TAskbI2lHVgA3IxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Answer ID" = _t, Sales = _t, Marketing = _t, Finance = _t]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Answer ID"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "null"))
in
#"Filtered Rows"Ricardo
2 Replies
Hi ibarraza ,
Try this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzEktBtJ5pTk5QMotMy8xLzlVKVYnWskIIeybWJSdWpKZlw4TAskbI2lHVgA3IxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Answer ID" = _t, Sales = _t, Marketing = _t, Finance = _t]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Answer ID"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "null"))
in
#"Filtered Rows"Ricardo
Thank you so much Ricardo
I needed to see the steps in M to apply it to a similar table.
Thank you