Forum Discussion
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
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
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)
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
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"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.
