cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
_n_MarianLein
Helper II
Helper II

Values in table change when expanding table column

Hello,


Didn't find anything via the search function...
I'm running a query with the following source code:

let
    Quelle = Latest_Tickets,
    #"Gefilterte Zeilen" = Table.SelectRows(Quelle, each ([Status] = "In Process")),
    #"Gruppierte Zeilen" = Table.Group(#"Gefilterte Zeilen", {"Ticket Type", "Processor"}, {{"Latest_Ticket_By_Type_By_Processor", each Table.RowCount(_), type number}}),
    #"Zusammenführte Abfragen" = Table.NestedJoin(#"Gruppierte Zeilen", {"Ticket Type"}, Order_Ticket_Type, {"Type"}, "Order_Ticket_Type", JoinKind.LeftOuter),
    #"Erweiterte Order_Ticket_Type" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "Order_Ticket_Type", {"Reihenfolge"}, {"Order_Ticket_Type.Reihenfolge"}),
    #"Sortierte Zeilen" = Table.Sort(#"Erweiterte Order_Ticket_Type",{{"Order_Ticket_Type.Reihenfolge", Order.Ascending}})
in
    #"Sortierte Zeilen"

Within the marked code, the values change in the table. I don't konw how to describe it better:

Value at "Zusammengeführte Abfragen"Value at "Zusammengeführte Abfragen"Value at "Erweiterte Order..."Value at "Erweiterte Order..."

Would anybody have an idea why this occurs?!

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Hi @_n_MarianLein ,

there is no native sort order in Power Query and the joins do often reorder the rows.

If you want to keep a sort order from the original load, you have to add an Index-column right at the beginning after which you can sort later on, once PQ has changed the row.

But make sure to buffer the sort, as the sort order might not be kept otherwise: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-i... 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
ImkeF
Super User
Super User

Hi @_n_MarianLein ,

there is no native sort order in Power Query and the joins do often reorder the rows.

If you want to keep a sort order from the original load, you have to add an Index-column right at the beginning after which you can sort later on, once PQ has changed the row.

But make sure to buffer the sort, as the sort order might not be kept otherwise: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-i... 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Nathaniel_C
Super User
Super User

Hi @_n_MarianLein ,

Let me suggest that you reach out to @ImkeF  who is a genius with queries!


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors