Forum Discussion
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: Address Current Owner Last Owner Lega...
Good day juliannegibson,
This code,
- Groups by "Address".
- 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
Solution Sage
Solution SageGood day juliannegibson,
This code,
- Groups by "Address".
- 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...