Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Matching data within the same appended table

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

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

4 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    Anonymous 

     

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

     

     

    If this helps, mark it as a solution.

    Kudos are nice too.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        VasTg
        Memorable Member
        6 years ago

        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.

         

         

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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.