Forum Discussion
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 ...
Fields could be doubled if keys are not unique. However, it's better to discuss on the sample - unique or not keys, which kind of join, etc
So as far as i know the key is unique. I made a separate query with each state and then added an index column 1 to 50. Added that key to both Harvested and Crop Report and then joined with inner.
It looks like duplication of https://techcommunity.microsoft.com/t5/excel/power-query-doubling-some-fields-after-creating-key-and-merging/m-p/4228920
But why do you need State ID if could merge by State Name. If ID, are they exactly the same for each state in all tables? By the way, you have left join, not inner join.
- I did start with just using the state name....but but was getting the same issue, and I have check to see if exactly the same in each table. That post was by me....i received an error when posting but must have gone through.
Does that logic
let Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content], DeclareType = Table.TransformColumnTypes( Source, { {"State", type text} , {"Value", Int64.Type} }), MergeState = Table.NestedJoin( DeclareType, {"State"}, State, {"State"}, "State.1", JoinKind.LeftOuter), ExpandedState = Table.ExpandTableColumn( MergeState, "State.1", {"State name"}, {"State name"}), MergeHarvested = Table.NestedJoin( ExpandedState, {"State"}, Harvested, {"State"}, "Harvested", JoinKind.LeftOuter), ExpandHarvested = Table.ExpandTableColumn( MergeHarvested, "Harvested", {"Pct"}, {"Pct"}) in ExpandHarvestedclose to what you do?