Combining Duplicate data into one

[glenreyes]

Hi,

I am new to the PBI scene and have been searching for a solution to combine two data records into one. I have viewed trasnform, group by , etc... and so far all the steps I followed did not work out or I may be just doing something wrong or the solutions is not even what I am looking for. So the issue i am having is that I have a dashboard that I have created that shows the computer and the users associated/assigned to the computers. Whats happening to the data is that the computer gets duplicated values due to multiple users that are using the same computer. What I would like to see if there is a way to merge both users to display in one row which references the same computer. Hope I explained it right. I have highlighted the issue I am having and hope there is a way to change the view that the users using the same computer can be displayed at the same time but only counting one computer... Thank you for reviewing my post and hope that someone would be able to help me...

[v-yuezhe-msft · Accepted solution]

@glenreyes,

Please add a blank query in your Power BI Desktop and paste the following code to advanced Editor of the blank query. The Group Rows code performs the combination.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg72MjQwMDVQ0lFyBGJnAyPdEEMLXVMTSwtzIN8ltTi7JL8AyPIvKMkMyEmtUDA3MAGrzktJLVeK1YEbYQIUdEI1wsgYrxG+iUWZSak5lBkSnFqUnpmvFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ASSET_NUMBER = _t, SERIAL_NUMBER = _t, NETWORK_NAME = _t, ASSET_TYPE = _t, MODE_NAME = _t, ClientName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ASSET_NUMBER", type text}, {"SERIAL_NUMBER", type text}, {"NETWORK_NAME", type text}, {"ASSET_TYPE", type text}, {"MODE_NAME", type text}, {"ClientName", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ASSET_NUMBER", "SERIAL_NUMBER", "NETWORK_NAME", "ASSET_TYPE", "MODE_NAME"}, {{"ClientName", each Text.Combine([ClientName], ", "), type text}})
in
    #"Grouped Rows"

Regards,
Lydia

[glenreyes]

I followed your suggestion and looked at the code which I take it you created by groupby. I did that for the columns I would like to combine from my SQL DB and the results are below. What I would like to ask if you can provide steps on how to inject the code you provide into this in order to proude the sample output you provided.

let
    Source = Sql.Databases("sqlserv1\sqlnch3"),
    WebHelpDesk = Source{[Name="WebHelpDesk"]}[Data],
    dbo_vw_ASSET_INVENTORY_test = WebHelpDesk{[Schema="dbo",Item="vw_ASSET_INVENTORY_test"]}[Data],
    #"Grouped Rows" = Table.Group(dbo_vw_ASSET_INVENTORY_test, {"ASSET_NUMBER", "NETWORK_NAME", "PURCHASE_DATE", "SERIAL_NUMBER", "ASSET_TYPE", "MODEL_NAME", "NAME", "ClientName"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

[glenreyes]

Thank you the solution you have provided worked. I just regrouped all the comumns/rows that I want to combine to produce the single data in the client names and it worked. I just added the last piece {{"ClientName", each Text.Combine([ClientName], ", "), type text}}) in #"Grouped Rows" to combine the client names.

[glenreyes]

Hello thank you so much for replying. I have added the following code and it partially worked. I have posted the results and the top result is the orginal report and the bottom one is the report produced from the code you provided. What I would like to ask you how I can complete the combined report showing the following:

1. show all the other assets in the DB.

2. Complete serial number

3. The combined client names associated to the asset by first and last names.

Sorry I had to blurr the serial number in order to keep the info private. The standard format consist of 7 - 10 alpha and numerical (e.g. 7abcde3)

[glenreyes]

This is what I have in my advance query. If I want to add your code so that it syncs with the tables I want and display the grouped data, how will I inject your code into this?

let
    Source = Sql.Databases("sqlserv1\sqlnch3"),
    WebHelpDesk = Source{[Name="WebHelpDesk"]}[Data],
    dbo_vw_ASSET_INVENTORY_test = WebHelpDesk{[Schema="dbo",Item="vw_ASSET_INVENTORY_test"]}[Data]
in
    dbo_vw_ASSET_INVENTORY_test

[glenreyes]

Adding a link: https://community.powerbi.com/t5/Desktop/Combine-values-of-multiple-rows-in-one-row/td-p/308919

I saw by grouping the column, but when I followed the steps I got the following error.