Forum Discussion

glenreyes's avatar
glenreyes
Frequent Visitor
7 years ago
Solved

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 w...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
glenreyes
Frequent Visitor
7 years ago

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's avatar
glenreyes
Frequent Visitor
7 years ago

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