Combing/Merging ROWS

[juliannegibson]

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:

Address	Current Owner	Last Owner	Legal Description
1600 Pennsylvania Avenue NW, Washington, DC 20500	Joe 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?

[collinsg · Accepted solution]

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