Combing Data Fields

[ODash]

Hi,

I'm relativly new to Power BI and am at the exploring stage to see if it can work for me.

I've got to a point of I know what I'd like to do, but not even sure if it's possible, so hopefully someone can assist.

I currently have a table of references, with aspects for each as below.

Ref	Aspect 1	Aspect 2	Aspect 3
1	A	B	C
2	B
3	C	A
4	A

Each ref can have multiple aspects, and the number of the aspect is largely irrelavent for my purposes, so I'd like to combine the aspects into one column. So for example I can search for all References that have an aspect of A.

I believe if I can format the table as below it will work in BI, but as it's a large dataset I'm not sure that a manageable solution.

Ref	Aspect
1	A
1	B
1	C
2	B
3	C
3	A
4	A

I'd welcome any thoughts please and let me know if you need any further explaination.

Many Thanks,

Ollie

[v-jayw-msft · Accepted solution]

Hi @ODash ,

You can also use the unpivot feature and remove empty feature in Query Editor.

Please check the following steps.

1. Select the tree columns and click unpivot.

2. Click the buttom at the right of value column and click remove empty.

3. Remove the Attribute column.

The result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

Hi @ODash ,

You just need to unpivot the other columns.

Here is the code, just past on Advanced Editor on Power Query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYicgdlaK1YlWMoLyFMAYJGIMloOog4iYwHlgkVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Ref = _t, #"Aspect 1" = _t, #"Aspect 2" = _t, #"Aspect 3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref", Int64.Type}, {"Aspect 1", type text}, {"Aspect 2", type text}, {"Aspect 3", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Ref"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " "))
in
#"Filtered Rows"

Ricardo