Help with randomly ordered data

[KamK]

Hi Guys ,

 Im hoping to get some help the resident experts here .

I have a datataset (currently in MS Excel) that looks like the following mock up . The real dataset has anything from none to  200 separate attributes and they appear in a random order.

Im hoping to transform this into a useable format and eventually create a unique list of Attributes and use it a slicer . I.e. Number of customers that have attribute "apple".

Any help would be appreciated.

Kam

[GilbertQ]

@KamK if you are using Excel or Power BI and using Power Query, what you can do, is to split your attribute column by clicking on the column once in the Query Editor and selectings "Split By" and then for the delimeter select comma, and make sure that under split you leave it with the default "At each occurrence of the delimiter"

[KamK]

Hi @GilbertQ Thank you for your resposnse. The issue I have is that If I use text to columns / split by delimeter in either excel or Power BI  my attributes will end up in different columns .   based on current example customer Joe Bloggs would have apple =1 in column D but John Smith would have apple=1 in column E

This is the part im struggling with

Cheers

Kam

[GilbertQ · Accepted solution]

@KamK I now understand what you are trying to do.

I would suggest the following:

1. Do the first split by column to get them all into seperate columns.
2. I would then split each column again this time using the equals delimiter "=" so that you can get the value for each attribute?
   1. Unless it is always 1
3. I would then select all the columns and then Unpivot the data.
   1. What this will do, is to then bring all the columns together and in rows.
   2. This should bring both the attribute and the value.

Now that it is all running in rows, and you have got all the attributes, that means you would have all the attributes in one column?

[KamK]

@GilbertQThank you so much for your help. This worked jsut as you described.

[GilbertQ]

@KamK glad that it solved your issue.

[KamK]

@GilbertQ Thank you for your quick responses .  The value is always = 1 so thats probaly not an issue . This unpivot concept is new to me. I shall give it crack with my dummy data and see where it gets me . Thank you again for your help

regards

Kam

[v-haibl-msft]

@KamK

Just follow guavaq's solution, it should work well. Please post back if you still have problem on it.

Best Regards,

Herbert

[GilbertQ]

@KamK glad to help out and if the =1 is not required then I would remove by using the "replace values" and replace it with "" or blank. Just to make it easier later.