create table with unique values from another table columns

[ibarraza]

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!! 

[camargos88 · Accepted solution]

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

[ibarraza]

Thank you so much Ricardo

I needed to see the steps in M to apply it to a similar table.

Thank you