Results 1 to 8 of 8

Thread: The Merge window shows correct results, but the Expand shows a wrong result.

  1. #1
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    70
    Articles
    0
    Excel Version
    Office 365

    The Merge window shows correct results, but the Expand shows a wrong result.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Question posed in the german ms-office-forum.de:

    In a list with many timestamps (Input worksheet), there can be one or more IDs for each timestamp. Now the questioner wants to know which ID was booked first in a list of manually entered timestamps. But if the ID "FM0" exists for a timestamp, then this ID should be reported as the result.

    With a formula combination of COUNTIFS and VLOOKUP, it is no problem to get the correct result. But since the questioner asked for a VBA solution (many users don't know that very often Power Query can be used as well), I also developed a solution with Power Query.

    For all timestamps only the first ID or, if available, the FM0 ID is determined and only this one is loaded as connection. In the evaluation query the ID of the timestamps is determined by merging them. The Merge window also shows the correct IDs. But as soon as I expand the result of the merge in the next step, the FM0 IDs are no longer shown.

    I have tested my PQ query several times and cannot find any errors in it. Therefore I think this must be an error in Power Query.

    Some weeks ago I already reported a bug: https://www.excelguru.ca/forums/show...-automatically

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,414
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I'll admit that I'm not thrilled with this answer, but it's not classified as a bug, but more how the engine works. To get around this problem - in the step before the merge - you either need to:
    • Wrap the step in Table.Buffer()
    • Add an Index Column, then Remove the Index column (but both steps must be there)


    That essentially locks in the state before the merge takes place, and the correct results should pass through.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    70
    Articles
    0
    Excel Version
    Office 365
    Thank you very much for the very quick reply !!!

    I have incorporated your suggestions into the query, but it has no effect on me.

    let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
    Change_Types = Table.Buffer(Table.TransformColumnTypes(Source,{{"Datum", type datetime}})),
    #"Added Index" = Table.AddIndexColumn(Change_Types, "Index", 0, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index"}),
    Merge_Queries = Table.NestedJoin(#"Removed Columns", {"Datum"}, Load_Input_as_connection, {"Datum start"}, "Load_Input_as_connection", JoinKind.LeftOuter),
    Expand_Merge = Table.ExpandTableColumn(Merge_Queries, "Load_Input_as_connection", {"ID"}, {"ID"}),
    Replace_null = Table.ReplaceValue(Expand_Merge,null,"not found",Replacer.ReplaceValue,{"ID"})
    in
    Replace_null

  4. #4
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    70
    Articles
    0
    Excel Version
    Office 365
    Now with following code it works:

    let

    tb_input = Table.Buffer(Load_Input_as_connection),

    Source = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
    Change_Types = Table.Buffer(Table.TransformColumnTypes(Source,{{"Datum", type datetime}})),


    #"Added Index" = Table.AddIndexColumn(Change_Types, "Index", 0, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index"}),
    // Merge_Queries = Table.NestedJoin(#"Removed Columns", {"Datum"}, Load_Input_as_connection, {"Datum start"}, "Load_Input_as_connection", JoinKind.LeftOuter),
    Merge_Queries = Table.NestedJoin(#"Removed Columns", {"Datum"}, tb_input, {"Datum start"}, "tb_input", JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(Merge_Queries, "tb_input", {"ID"}, {"ID"}),

    Replace_null = Table.ReplaceValue(#"Expanded {0}",null,"not found",Replacer.ReplaceValue,{"ID"})
    in
    Replace_null

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,414
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Yeah, you beat me to it. It looks like the other table is the one that needed the buffering. I ended up adjusting the original code sample to use this:

    Code:
    	Merge_Queries =
    		Table.NestedJoin(
    			Table.Buffer(Change_Types),
    			{"Datum"},
    			Table.Buffer(Load_Input_as_connection),
    			{"Datum start"},
    			"Load_Input_as_connection",
    			JoinKind.LeftOuter
    		)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    70
    Articles
    0
    Excel Version
    Office 365
    In any case many, many thanks for your very fast help.

    Maybe you can find some time to have a look at the other case.

  7. #7
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    70
    Articles
    0
    Excel Version
    Office 365
    Hello, Ken,

    after it was running, I tested the query again and cleaned it up for unnecessary steps. The definition of the Table.Buffer is actually only necessary for the first table.

    But I have merged tables so often by now and normally I never need the Table.Buffering. And in those queries where I had inserted it for testing purposes to see if it would make the queries faster, they usually became slower.

    So I don't understand why this query returns wrong results when Table.Buffering is not used.

    The crazy thing is that I had the same error when I loaded the first table into the folder and re-imported it for the merge instead of just as a connection.

    On the other hand I also had the following experience:
    I read in raw data with a query, prepared it and loaded it as a sheet into the Workbook. With another query I then imported this sheet to create an evaluation. Loading it as a table into the Workbook was not really necessary. But for testing the 2nd query it was helpful to have this data present.

    So and now the interesting effect: If I made changes in the raw data table and did not call the first query, with which the raw data are read and rooted up, but the update of the 2nd query has nevertheless considered the changes of the raw data. And this although the changes were not yet visible in the loaded raw data table.

    I could only prevent this effect by outsourcing the reading, processing and loading of the raw data into a separate workbook.

    So today I can only say that Power Query is really a fantastic tool, but many things are still mystical for me.

    But now I attach the cleaned up folder once again.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,414
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    The fundamental issue is that there are a number of operations which don’t preserve sort order, and these include distinct, group and join. We've brought this up with Microsoft, but it is something deep within the engine, so I don't imagine it will ever get changed. Basically, when you hit one of these, the action you need to take is either Buffer() the correct table/step, or trigger this by adding/removing an Index column (which essentially assigns values and forces the same behaviour.)

    I agree that it is less than ideal, but it underscores the importance of always checking the output.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •