Log In
Register
Home
February 8, 2020
Offline
After grouping numbers with a rank, if altering the data within that group, a merge retrieves the wrong data. It works fine at first but if I amend the data, the merge is pulling incorrect data (it's like it's pulling the original merge and not the amended data.
For example, if the grouped data is ranked as 1 and 2 and I amend something that reverses that ranking to 2 and 1, a new merge pulls the old data. It's not recognizing the altered data.
It's a very large file so I tried to create a small similar example with the attached file. Basically, we have a table that allows a column for manual adjustments. This table then feeds other queries. Again, when first setup it works but if you adjust the manual column, the merging later on is not pulling the correct data.
February 8, 2020
Offline
I am fairly certain my "error" occurs within the grouping and ranking (indexing). Is there a way to group/sort/rank all in one step and solve my problem?
#"Sorted Rows1" = Table.Sort(#"Expanded DraftPrep",{{"Round Goal", Order.Ascending}, {"Round Rank", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows1", {"Round Goal"}, {{"Count", each _, type table [Player Name=nullable text, ORID=nullable number, C=nullable text, 1B=nullable text, 2B=nullable text, 3B=nullable text, SS=nullable text, #"C/IF"=nullable text, OF=nullable text, UT=nullable text, Multi=nullable text, ADP=text, zScore=text, P=nullable text, RP=nullable text, SP=nullable text, Injury=text, Status=text, Est. Return=text, INJ=text, Pos=any, Round Goal=nullable text, Round Rank=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Round#(lf) Rank",1,1)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Player Name", "ORID", "C", "1B", "2B", "3B", "SS", "C/IF", "OF", "UT", "Multi", "ADP", "zScore", "P", "RP", "SP", "Injury", "Status", "Est. Return", "INJ", "Pos", "Round#(lf) Rank"}, {"Player Name", "ORID", "C", "1B", "2B", "3B", "SS", "C/IF", "OF", "UT", "Multi", "ADP", "zScore", "P", "RP", "SP", "Injury", "Status", "Est. Return", "INJ", "Pos", "Round#(lf) Rank"}),
October 5, 2010
Offline
Hi,
I had a hard time understanding the issue because your explanation wasn't specific and the file had 4 queries so I had to wade through it all and try to figure out exactly where the issue was. Please be as specific as possible about which query the problem is with - you have more than 1 query doing a merge.
It's always a good idea to show an example of what is wrong, and show what you want as your expected result. Hopefully I've understood and figured this out.
I think the issue is due to the way Power Query stores and displays data - which isn't consistent. That is, it'll show you one thing but store it another way. Very annoying.
In this case you need to use the Table.Buffer function to fix it, I wrote about this here Dense Ranking in Power Query
What happens is that Power Query has a list (table column) and in the editor it shows you that the list is 1, 2, 3, 4, 5 but internally it could be actually storing it as 5, 2, 1, 4 ,3 so when you come to use that list, you get unexpected results.
To force PQ to store the data in the same way it shows it to you, you can use buffer function, in this case Table.Buffer.
You'll see in the attached file that I've created a query called Merge with Buffer.
The first two steps are to buffer the tables you want to merge
WatchListBuffer = Table.Buffer(#"Watch List Load"),
TestBuffer = Table.Buffer(Test),
then merge those buffered tables
Source = Table.NestedJoin(WatchListBuffer, {"ORID"}, TestBuffer, {"ORID"}, "Test", JoinKind.LeftOuter),
The result looks like it's what you want, please check.
Regards
Phil
February 8, 2020
Offline
Thanks Phil. You got the idea of what I was trying to accomplish even though I could not explain the situation well. I am certain someone who knows what they are doing would have done better.
I had read about Table.Buffer but really could not comprehend how to put it together. Plus, I read about it potentially slowing down the process (but does not seem to be the case here).
I am going to take this concept to the much larger file, see if I can pull everything together even with other queries. Thanks for posting the file as an example and I appreciate the time to help. I think I get the concept here, following the code.
October 5, 2010
Offline
No worries, good luck.
Phil
February 8, 2020
Offline
Final post. I was able to incorporate this into the larger file with multiple queries and is working as intended. I was originally looking to stay away from the Table.Buffer concept as I had read about potential query slowdown (this was why my thinking was perform sorts within grouping, which, I was not familiar with). Table.Buffer did NOT cause a slowdown in my example and works perfectly.
My apologies to all with my difficulties in trying to explain my situation.
1 Guest(s)
