Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
juliannegibson
New Member

Combing/Merging ROWS

I have a historical data set that was not properly joined. I am unable to fix this and I must use this data set. Here is an example of what it looks like:

AddressCurrent OwnerLast OwnerLegal Description
1600 Pennsylvania Avenue NW, Washington, DC 20500Joe Biden  
1600 Pennsylvania Avenue NW, Washington, DC 20500 Donald Trump 
1600 Pennsylvania Avenue NW, Washington, DC 20500  White House

 

So in the example, I have three rows that all have only one column of data for that address. I am trying to merge rows with the same address to fill all the nulls. I have some addresses that have up to 20 duplicates. Is there any way I can do this in power query?

1 ACCEPTED SOLUTION
collinsg
Super User
Super User

Good day juliannegibson,

This code,

  1. Groups by "Address".
  2. For each column other than "Address" finds the "max" value in that column and adds a column to contain that max value. This assumes the cells in each column contain null or a value and all non-null values in the column are the same.

This may or may not help as there is room for other interpretations of your post.

Anyhow, hope this helps

 

The key step is called #"Grouped Rows". The previous steps are just getting to the point where it is as if I had loaded your data.

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WMjQzMFAISM3LK67MKUvMy0xUcCxLzStNVfAL11EITyzOyMxLL8nP01FwcVYwMjA1MFDSUfLKT1VwykxJzQOyFcA4Voc8k0C6XfLzEnNSFEKKSnMLKDYMhMMzMktSFTzyS4tTlWJjAQ==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Address = _t, #"Current Owner" = _t, #"Last Owner" = _t, #"Legal Description" = _t]
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
            {"Address", type text},
            {"Current Owner", type text},
            {"Last Owner", type text},
            {"Legal Description", type text}
        }
    ),
    #"Grouped Rows" = Table.Group(
        #"Changed Type",
        {"Address"},
        {
            {"Current Owner", each List.Max([Current Owner]), type nullable text},
            {"Last Owner", each List.Max([Last Owner]), type nullable text},
            {"Legal Description", each List.Max([Legal Description]), type nullable text}
        }
    )
in
    #"Grouped Rows"

 yielding...

collinsg_0-1706811620075.png

 

View solution in original post

1 REPLY 1
collinsg
Super User
Super User

Good day juliannegibson,

This code,

  1. Groups by "Address".
  2. For each column other than "Address" finds the "max" value in that column and adds a column to contain that max value. This assumes the cells in each column contain null or a value and all non-null values in the column are the same.

This may or may not help as there is room for other interpretations of your post.

Anyhow, hope this helps

 

The key step is called #"Grouped Rows". The previous steps are just getting to the point where it is as if I had loaded your data.

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WMjQzMFAISM3LK67MKUvMy0xUcCxLzStNVfAL11EITyzOyMxLL8nP01FwcVYwMjA1MFDSUfLKT1VwykxJzQOyFcA4Voc8k0C6XfLzEnNSFEKKSnMLKDYMhMMzMktSFTzyS4tTlWJjAQ==",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Address = _t, #"Current Owner" = _t, #"Last Owner" = _t, #"Legal Description" = _t]
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
            {"Address", type text},
            {"Current Owner", type text},
            {"Last Owner", type text},
            {"Legal Description", type text}
        }
    ),
    #"Grouped Rows" = Table.Group(
        #"Changed Type",
        {"Address"},
        {
            {"Current Owner", each List.Max([Current Owner]), type nullable text},
            {"Last Owner", each List.Max([Last Owner]), type nullable text},
            {"Legal Description", each List.Max([Legal Description]), type nullable text}
        }
    )
in
    #"Grouped Rows"

 yielding...

collinsg_0-1706811620075.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors