Data format

[vinb4u2]

I have table  like  below

Appid

OS

APP0005	WINDOWS SERVER 2012 DATA CENTER
APP0005	WINDOWS SERVER 2016
APP0005	WINDOWS SERVER 2019
APP0006	WINDOWS SERVER 2012 R2 STANDARD
APP0006	WINDOWS SERVER 2016
APP0006	WINDOWS SERVER 2019
APP0006	WINDOWS SERVER 2019
APP0005	WINDOWS SERVER 2019

and I need to format like below .  please suggest 

App id

OS

APP0005	WINDOWS SERVER 2012, DATA CENTER,WINDOWS SERVER 2016,WINDOWS SERVER 2019
APP0006	WINDOWS SERVER 2012 R2 STANDARD,WINDOWS SERVER 2016,WINDOWS SERVER 2019

[amitchandak]

@vinb4u2 , Please check the solution suggested by Sahir_Maharaj first.

I think you have two columns

Take App in visual (not summarized) with this measure can do

concatenatex(Table, Table[OS], ", ")

Concatenatex: https://www.youtube.com/watch?v=du2HSEzng2E&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=43

[Sahir_Maharaj]

1. To format the "OS" column with comma-separated values, select the column and click on the "Split Column" button in the "Transform" tab.

2. In the "Split Column" dialog box, select "Custom" as the "Split type" and enter a comma "," as the delimiter.

3. This will split the "OS" column into multiple columns with the same name.

4. Finally, select all the new "OS" columns and click on the "Merge Columns" button in the "Transform" tab.

5. In the "Merge Columns" dialog box, select a comma "," as the delimiter and enter "OS" as the new column name.

6. Click "OK" to apply the changes.

This should give you the desired format.  Let me know if you might require further assistance.

[vinb4u2]

Hi Sahir,

For some reason Merge option not enabled in my power BI desktop

[Sahir_Maharaj]

Hello @vinb4u2,

1. Load your table into Power Query Editor by selecting "Edit Queries" from the Home tab in Power BI Desktop.

2. Select your table and click on the "Group By" button in the "Transform" tab.

3. In the "Group By" dialog box, select "Appid" as the "Group by" column.

4. In the "New column name" field, enter "OS" (or any name you prefer).

5. In the "Operation" column, select "All Rows".

6. Click "OK" to apply the changes.

7. This will create a new table with a column for "Appid" and a column for "OS" where the values in the "OS" column are concatenated for each "Appid".

[vinb4u2]

The above funtion not removing duplicates from Appid and not combining muliple OS value in single row using ',' as a seperator