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