cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
glenreyes Frequent Visitor
Frequent Visitor

Combining Duplicate data into one

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

 

 

Capture2.jpg

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Combining Duplicate data into one

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
glenreyes Frequent Visitor
Frequent Visitor

Re: Combining Duplicate data into one

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.

 

Capture3.JPG

Moderator v-yuezhe-msft
Moderator

Re: Combining Duplicate data into one

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
glenreyes Frequent Visitor
Frequent Visitor

Re: Combining Duplicate data into one

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)

 

 

Capture4.JPG

glenreyes Frequent Visitor
Frequent Visitor

Re: Combining Duplicate data into one

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 Frequent Visitor
Frequent Visitor

Re: Combining Duplicate data into one

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 Frequent Visitor
Frequent Visitor

Re: Combining Duplicate data into one

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.