Forum Discussion

joelrayson's avatar
joelrayson
Copper Contributor
Aug 26, 2024

Power Query Doubling some fields after creating key and merging queries.

I have two queries in Power Query that I need to join together.  I have created a key so each state has its own number.  When I merge my Crop Report Query with my Harvested Query some states doubled up with different data coming from somewhere? My code below was filtered down to a single state that is showing the problem.

 

 #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"agg_level_desc", type text}, {"watershed_code", Int64.Type}, {"begin_code", Int64.Type}, {"load_time", type datetime}, {"group_desc", type text}, {"Value", Int64.Type}, {"freq_desc", type text}, {"week_ending", type date}, {"short_desc", type text}, {"domain_desc", type text}, {"state_name", type text}, {"state_ansi", Int64.Type}, {"unit_desc", type text}, {"asd_code", type any}, {"asd_desc", type any}, {"domaincat_desc", type text}, {"zip_5", type any}, {"watershed_desc", type any}, {"state_fips_code", Int64.Type}, {"country_code", Int64.Type}, {"prodn_practice_desc", type text}, {"reference_period_desc", type text}, {"location_desc", type text}, {"class_desc", type text}, {"statisticcat_desc", type text}, {"sector_desc", type text}, {"country_name", type text}, {"year", Int64.Type}, {"region_desc", type any}, {"util_practice_desc", type text}, {"end_code", Int64.Type}, {"county_name", type any}, {"county_code", type any}, {"congr_district_code", type any}, {"county_ansi", type any}, {"CV (%)", type any}, {"source_desc", type text}, {"commodity_desc", type text}, {"state_alpha", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"watershed_code", "begin_code", "state_ansi", "asd_code", "asd_desc", "domaincat_desc", "zip_5", "watershed_desc", "state_fips_code", "country_code", "prodn_practice_desc", "class_desc", "country_name", "region_desc", "util_practice_desc", "county_name", "county_code", "congr_district_code", "county_ansi", "CV (%)", "source_desc", "Source.Name"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"load_time", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"state_name", "state_alpha", "agg_level_desc", "freq_desc", "week_ending", "load_time", "group_desc", "short_desc", "unit_desc", "Value", "domain_desc", "reference_period_desc", "location_desc", "statisticcat_desc", "sector_desc", "year", "end_code", "commodity_desc"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"load_time"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"state_name", "state_alpha", "agg_level_desc", "freq_desc", "week_ending", "group_desc", "short_desc", "unit_desc", "Value", "domain_desc", "reference_period_desc", "end_code", "year", "location_desc", "statisticcat_desc", "sector_desc", "commodity_desc"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns1",{"domain_desc"}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Removed Columns2",{"location_desc", "state_name", "state_alpha", "agg_level_desc", "year", "freq_desc", "reference_period_desc", "end_code", "week_ending", "sector_desc", "group_desc", "statisticcat_desc", "commodity_desc", "short_desc", "unit_desc", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",{{"location_desc", "State"}, {"state_name", "State Name"}, {"state_alpha", "State 2"}, {"year", "Year"}, {"reference_period_desc", "Week of Year"}, {"week_ending", "Week Ending"}, {"commodity_desc", "Crop"}, {"unit_desc", "Condition"}, {"Value", "Pct"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each true),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Region", each if List.Contains({"VA","MD", "NC", "SC", "TN", "GA", "AL", "MS", "FL"}, [State 2]) then "Region 3"
        else if List.Contains({"ME", "VT", "NH", "MA", "RI", "CT", "NJ", "DE", "WV"}, [State 2]) then "Region 1" 
        else if List.Contains({"IA", "MO", "AR", "LA", "KS", "OK", "TX"}, [State 2]) then "Region 5" 
        else if List.Contains({"MN", "SD", "ND", "NE", "CO", "UT", "ID"}, [State 2]) then "Region 4" 
        else if List.Contains({"WI", "MI", "OH", "IL", "IN", "KY", "PA", "NY"}, [State 2]) then "Region 2" 
        else if List.Contains({"WA", "OR", "CA", "AZ", "NM", "NV", "MT", "WY", "AK", "HI"}, [State 2]) then "Region 6" 
    
    
    else null, type text),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Week of Year] = "WEEK #33")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows1", {"State"}, State, {"State"}, "State.1", JoinKind.LeftOuter),
    #"Expanded State.1" = Table.ExpandTableColumn(#"Merged Queries", "State.1", {"State ID"}, {"State ID"}),
    #"Reordered Columns3" = Table.ReorderColumns(#"Expanded State.1",{"State ID", "State", "State Name", "State 2", "agg_level_desc", "Year", "freq_desc", "Week of Year", "end_code", "Week Ending", "sector_desc", "group_desc", "statisticcat_desc", "Crop", "short_desc", "Condition", "Pct", "Region"}),
    #"Merged Queries1" = Table.NestedJoin(#"Reordered Columns3", {"State ID"}, #"Harvested (2)", {"State ID"}, "Harvested (2)", JoinKind.LeftOuter),
    #"Expanded Harvested (2)" = Table.ExpandTableColumn(#"Merged Queries1", "Harvested (2)", {"Pct Harvested"}, {"Pct Harvested"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Harvested (2)",{{"Condition", Order.Ascending}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Sorted Rows",{"State Name"}),
    #"Filtered Rows2" = Table.SelectRows(#"Removed Columns3", each ([State] = "ARKANSAS") and ([Crop] = "SOYBEANS"))
in
    #"Filtered Rows2"

 

   

Resources