Matching data within the same appended table

[Anonymous]

I appended two tables that had the same ID and am looking to autofill/match data from one row across all rows in a new column so I can use it as a filter across all my data. Example of my data is below with what I am hoping to accomplish in italics. Appreciate any help!

ID  County    County.Filter

1    Orange    Orange

1                    Orange 

2    Lake         Lake     

2                    Lake

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Assume your data is like that.

We can use merge instead of append to work on it.

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvSsxLT1WK1YlWMgJyfRKzgZxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, County = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"County", type text}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YGRRjAyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Table (3)", {"ID"}, "Table (3)", JoinKind.LeftOuter),
    #"Expanded Table (3)" = Table.ExpandTableColumn(#"Merged Queries", "Table (3)", {"County"}, {"Table (3).County"})
in
    #"Expanded Table (3)"

If it doesn't meet your requirement,  Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[VasTg]

@Anonymous 

Did you try the Fill->Down in Query editor?

If this helps, mark it as a solution.

Kudos are nice too.

[Anonymous]

Thanks for your reply @VasTg . It looks like it works, however my concern is that I'm creating the report to be used as a standard template across our organization (the data source would be changed at different locations). Please correct me if I'm wrong (as I'm new to Power BI), but wouldn't I have to go through this process every time I add a new data source?

[VasTg]

@Anonymous 

What is the datasource? You should be fine just by  pointing to the different file or datasource.All the applied steps will still be there.