Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi I am looking at trying to merge two field rows based on a duplicate value
ID | RefID (Duplicate Field) | Location (To Merge) | Area (To Merge) |
1 | 1 | UK | Cheshire |
2 | 1 | UK | Lancashire |
3 | 2 | USA | New York |
4 | 2 | USA | New York |
5 | 2 | UK | Cheshire |
6 | 3 | USA | New York |
7 | 4 | USA | New York |
8 | 4 | USA | Wisconsin |
Output
ID | RefID | Location | Area |
1 | 1 | UK | Cheshire; Lancashire |
3 | 2 | USA; UK | New York; Cheshire |
6 | 3 | USA | New York |
7 | 4 | USA; USA | New York; Wisconsin |
Also is it possible for the field to not have duplicate values such as “UK; UK; USA; UK; USA” but only have one unique value ie “UK; USA.
Thanks
Solved! Go to Solution.
@Anonymous - Try this Power Query script:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeNQbyDhnJFanJFZlKoUqxOtZIQs45OYl5yIkDMGCoHkQ4MdgaRfarlCZH5RNljKBLeUKUwKwy4zoIAxdk3mQAET7FIWKFLhmcXJ+XnFmXlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, RefID = _t, Location = _t, Area = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"RefID", Int64.Type}, {"Location", type text}, {"Area", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"RefID"}, {{"All Rows For RefID", each _, type table [ID=number, RefID=number, Location=text, Area=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID", each List.First([All Rows For RefID][ID])),
DistinctLocations = Table.AddColumn(#"Added Custom", "Location", each List.Sort(List.Distinct([All Rows For RefID][Location]))),
ConcatenateLocations = Table.TransformColumns(DistinctLocations, {"Location", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
DistinctAreas = Table.AddColumn(ConcatenateLocations, "Area", each List.Sort(List.Distinct([All Rows For RefID][Area]))),
ConcatenateAreas = Table.TransformColumns(DistinctAreas, {"Area", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Removed Columns" = Table.RemoveColumns(ConcatenateAreas,{"All Rows For RefID"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "RefID", "Location", "Area"})
in
#"Reordered Columns"
@Anonymous - Try this Power Query script:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeNQbyDhnJFanJFZlKoUqxOtZIQs45OYl5yIkDMGCoHkQ4MdgaRfarlCZH5RNljKBLeUKUwKwy4zoIAxdk3mQAET7FIWKFLhmcXJ+XnFmXlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, RefID = _t, Location = _t, Area = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"RefID", Int64.Type}, {"Location", type text}, {"Area", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"RefID"}, {{"All Rows For RefID", each _, type table [ID=number, RefID=number, Location=text, Area=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID", each List.First([All Rows For RefID][ID])),
DistinctLocations = Table.AddColumn(#"Added Custom", "Location", each List.Sort(List.Distinct([All Rows For RefID][Location]))),
ConcatenateLocations = Table.TransformColumns(DistinctLocations, {"Location", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
DistinctAreas = Table.AddColumn(ConcatenateLocations, "Area", each List.Sort(List.Distinct([All Rows For RefID][Area]))),
ConcatenateAreas = Table.TransformColumns(DistinctAreas, {"Area", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Removed Columns" = Table.RemoveColumns(ConcatenateAreas,{"All Rows For RefID"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "RefID", "Location", "Area"})
in
#"Reordered Columns"
Amazing this worked and have learnt more about Group By 🙂
You can do this in DAX using the following:
Table 2 = SUMMARIZE('Table1',[RefID (Duplicate Field)],"ID",MIN('Table1'[ID]),"Location",CONCATENATEX(DISTINCT('Table1'[Location (To Merge)]),[Location (To Merge)],";"),"Area",CONCATENATEX(DISTINCT('Table1'[Area (To Merge)]),[Area (To Merge)],";"))
@ImkeF should be able to provide a Power Query solution
Try using
https://docs.microsoft.com/en-us/dax/concatenatex-function-dax
If required also summarize function
https://docs.microsoft.com/en-us/dax/summarize-function-dax
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
78 | |
62 | |
61 | |
60 |
User | Count |
---|---|
160 | |
114 | |
100 | |
74 | |
65 |