Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Simon_29
Helper II
Helper II

The merged tables do not sort correctly

Hello,

I have a problem with mergeing tables and then sorting. The tables were merged correctly. As we can see in the first table, he gives me the first 3 records in a row. It should go as in the second picture, that is, it goes first: Badge, Name and so on. But in the first picture we see that the first 3 record shows: Badge, Badge, Badge, Name, Name, Name and so on. Could you tell me some trick to fix these 3 first records?

merge2.pngmerge1.png


Thank you very much.

1 ACCEPTED SOLUTION
Simon_29
Helper II
Helper II

Hello,

I found a solution. I added, before merging the query, an index column, which numbered my data from 0 to N ... When I used merge with the second table, it shuffled the whole index column, but I had it sorted again from 0 to N, so it was solved.



Thanks for your time 🙂

View solution in original post

5 REPLIES 5
Simon_29
Helper II
Helper II

Hello,

I found a solution. I added, before merging the query, an index column, which numbered my data from 0 to N ... When I used merge with the second table, it shuffled the whole index column, but I had it sorted again from 0 to N, so it was solved.



Thanks for your time 🙂

KNP
Super User
Super User

Can you share your Power Query code please so we can see the steps?

Some more detail about the tables you are merging would also be useful.

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Hello :),

of course:

let
FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
FileName = Excel.CurrentWorkbook(){[Name="FileName"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(FilePath & FileName), null, true),
#"Orders by Orders_Sheet" = Source{[Item="Orders by Orders",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Orders by Orders_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Badge", Int64.Type}, {"Name", type text}, {"SR Specialty", type text}, {"SR Type", type text}, {"Direct Rep Badge", Int64.Type}, {"Direct Rep Name", type text}, {"Role Name", type text}, {"Source Doc Type", type text}, {"Alpha Numeric Order Number", Int64.Type}, {"Order Number", Int64.Type}, {"Business Unit ID", Int64.Type}, {"Region", type text}, {"Data Source", type text}, {"Local Channel", type text}, {"Product Desc", type text}, {"Orig Cust Number", Int64.Type}, {"End User Cust Number", Int64.Type}, {"Account ID", Int64.Type}, {"Account Name", type text}, {"Account Type", type text}, {"Bill to Postal Code", Int64.Type}, {"Ship to Postal Code", Int64.Type}, {"Trx Type", type text}, {"Split Pct", Int64.Type}, {"Collab Deals Flag", type any}, {"Reason Code", type any}, {"Pre Processed Code", type any}, {"Period Name", type text}, {"Booked Date", type date}, {"Invoice Date", type date}, {"Processed Date", type date}, {"Created By", type text}, {"Creation Date", type datetime}, {"SBU", type text}, {"Vertical", type text}, {"Analyst Name", type text}, {"Adjustment Comments", type any}, {"BVRR Flag", type text}, {"Purchase Order", type text}, {"Orig Ref Order Num", type any}, {"Originating Flag", type text}, {"Price Floor Bucket", type any}, {"Price Floor Pct", Int64.Type}, {"Additional Commission Mod M1", Int64.Type}, {"Additional Commission Mod M2", Int64.Type}, {"Additional Commission Mod M3", Int64.Type}, {"ADSS Block Attainment Modifier", Int64.Type}, {"ADSS Block Attainment Modifier CEE", type any}, {"ADSS Block Attainment Modifier RTM", type any}, {"ADSS File Attainment Modifier", Int64.Type}, {"ADSS File Attainment Modifier CEE", type any}, {"ADSS File Attainment Modifier RTM", type any}, {"Analyst Cap", type any}, {"Analyst Floor", type any}, {"APEX Hybrid n Private Cloud Attainment Modifier", Int64.Type}, {"APEX HybridPrivate Cloud Attainment Modifier CEE", type any}, {"APEX HybridPrivate Cloud Attainment Modifier RTM", type any}, {"CCS B2B Rev", type any}, {"CCS HW Rev Chnl", type any}, {"CCS Rev", Int64.Type}, {"CCS Rev Chnl", type any}, {"CCS Rev OEM", type any}, {"CCS SNS Rev", type any}, {"CCS SNS ST Rev", type any}, {"CCS Svcs and Upsell Rev", type any}, {"Channel Preferred Uplift M1", type any}, {"Channel Preferred Uplift M2", Int64.Type}, {"Chnl Compute Mgn", type any}, {"Chnl Compute Storage Rev", type number}, {"Chnl Compute Svr Rev", Int64.Type}, {"Client Svr Ntwk Supp APOS Rev", Int64.Type}, {"Client Svr Ntwk Support Rev", Int64.Type}, {"Comm PC Attainment Modifier", Int64.Type}, {"Comm PC Direct Svcs Attainment Modifier", Int64.Type}, {"Comm PC MB Attainment Modifier", type any}, {"Cons SP Rev", Int64.Type}, {"Consulting Svcs Rev", type any}, {"CSB CSG Rev", type any}, {"CSB ISG Rev", type any}, {"CSB Storage Mgn", type any}, {"CSB Storage Rev", type any}, {"CSB TSR ISG Mgn", type any}, {"CSB TSR ISG Rev", type any}, {"CSG Direct Rev", type any}, {"CSG New Buyer Attainment Modifier", Int64.Type}, {"CSG New Buyer MB Direct Attainment Modifier", type any}, {"CSG Rev", type number}, {"CSG Rev CEE Chnl", type any}, {"CSG Rev Chnl", Int64.Type}, {"CSG Rev Chnl w 3PSW", Int64.Type}, {"CSG Rev Chnl w T1", type any}, {"CSG Rev Chnl w T1 n 3PSW", type any}, {"CSG Rev Direct", type any}, {"CSG Rev DTS", type any}, {"CSG Rev Intl", type any}, {"CSG Rev w 3PSW", type number}, {"CSG Rev w MSFT", type any}, {"DC SP Deploy Rev", Int64.Type}, {"DC SP Intl Rev", type any}, {"DC SP NA Rev", Int64.Type}, {"DC SP Rev VL", type any}, {"DCE Services Rev", type number}, {"DCE Services Rev Chnl", Int64.Type}, {"DCE Services SAE Rev", type any}, {"DCE Services SSR Rev", type any}, {"DCU VEC FOD Attainment Modifier", Int64.Type}, {"DCU VEC FOD Attainment Modifier RTM", type any}, {"DFS Metric 1", type any}, {"DFS Metric 2", type any}, {"DFS Metric 3", type any}, {"DPS Attainment Modifier", Int64.Type}, {"DPS Attainment Modifier CEE", type any}, {"DPS Attainment Modifier RTM", type any}, {"Ed Svcs Miles Mgn", type number}, {"Ed Svcs Miles Rev", type number}, {"Educ Svcs Rev", type number}, {"Educ Svcs Rev Excl Miles", Int64.Type}, {"Education Svcs Rev", type any}, {"EUC SP Rev", type any}, {"Faction Attainment Modifier", Int64.Type}, {"Faction Attainment Modifier RTM", type any}, {"FOD Svr Plus Attainment Modifier", Int64.Type}, {"FOD Svr Plus Attainment Modifier CEE", type any}, {"FOD Svr Plus Attainment Modifier RTM", type any}, {"GCP Attainment Modifier", Int64.Type}, {"GCP Attainment Modifier CEE", type any}, {"GCP Attainment Modifier RTM", type any}, {"ISG Rev", type number}, {"ISG Rev Chnl", type any}, {"MBO 1", Int64.Type}, {"MBO 2", Int64.Type}, {"MBO 3", Int64.Type}, {"Metric 1 MOD True Up", Int64.Type}, {"Metric 1 Sum All", type number}, {"Metric 1 Sum All - UNB Rev", type any}, {"Metric 2 MOD True Up", Int64.Type}, {"Metric 2 Sum All", type number}, {"Metric 2 Sum All - UNB Rev", type any}, {"Metric 3 MOD True Up", Int64.Type}, {"Metric 3 Sum All", type number}, {"PAN ISG Mgn", type number}, {"PM Campus Ntwk", type any}, {"PM ChromeBook", Int64.Type}, {"PM ChromeBook B2B", type any}, {"PM ChromeBook CEE", type any}, {"PM ChromeBook Chnl", Int64.Type}, {"PM ChromeBook SNS", type any}, {"PM Comm PC Direct DE", Int64.Type}, {"PM Connectrix", Int64.Type}, {"PM Connectrix B2B", type any}, {"PM Connectrix CEE", type any}, {"PM Connectrix RTM", type any}, {"PM Consumer", Int64.Type}, {"PM CSB Storage Rev", type any}, {"PM CSB Svr Plus", type any}, {"PM CSG New Buyer", Int64.Type}, {"PM Data Protection", Int64.Type}, {"PM Data Protection B2B", type any}, {"PM Data Protection CEE", type any}, {"PM Data Protection RTM", type any}, {"PM DC Ntwk", type any}, {"PM DP4400 Chnl", type any}, {"PM EI Ntwk CORE", Int64.Type}, {"PM EI Storage", Int64.Type}, {"PM EI Storage B2B", type any}, {"PM EI Storage CEE", type any}, {"PM EI Storage RTM", type any}, {"PM EI Svr Ntwk", Int64.Type}, {"PM EI Svr Ntwk B2B", type any}, {"PM EI Svr Ntwk CEE", type any}, {"PM EI Svr Ntwk RTM", type any}, {"PM EI Svr Ntwk SNS", type any}, {"PM EMC Select", Int64.Type}, {"PM Four Socket Solutions", Int64.Type}, {"PM Four Socket Solutions B2B", type any}, {"PM Four Socket Solutions CEE", type any}, {"PM Four Socket Solutions RTM", type any}, {"PM Four Socket Solutions SNS", type any}, {"PM HCI", type any}, {"PM Inspiron", Int64.Type}, {"PM Inspiron B2B", type any}, {"PM Inspiron CEE", type any}, {"PM Inspiron Chnl", Int64.Type}, {"PM Inspiron SNS", type any}, {"PM Latitude", type any}, {"PM Nutanix XC", Int64.Type}, {"PM Nutanix XC Series", Int64.Type}, {"PM Nutanix XC Series B2B", type any}, {"PM Nutanix XC Series CEE", type any}, {"PM Nutanix XC Series RTM", type any}, {"PM Nutanix XC Series SNS", type any}, {"PM ProDeploy CSG", Int64.Type}, {"PM ProDeploy CSG B2B", type any}, {"PM ProDeploy CSG CEE", type any}, {"PM ProDeploy CSG SNS", type any}, {"PM ProDeploy Plus CSG", Int64.Type}, {"PM ProDeploy Plus CSG B2B", type any}, {"PM ProDeploy Plus CSG CEE", type any}, {"PM ProDeploy Plus CSG SNS", type any}, {"PM ProDeploy Plus Svr", Int64.Type}, {"PM ProDeploy Plus Svr B2B", type any}, {"PM ProDeploy Plus Svr CEE", type any}, {"PM ProDeploy Plus Svr RTM", type any}, {"PM ProDeploy Plus Svr SNS", type any}, {"PM ProDeploy Svr", Int64.Type}, {"PM ProDeploy Svr B2B", type any}, {"PM ProDeploy Svr CEE", type any}, {"PM ProDeploy Svr RTM", type any}, {"PM ProDeploy Svr SNS", type any}, {"PM ProSupp Plus CSG", type number}, {"PM ProSupp Plus CSG B2B", type any}, {"PM ProSupp Plus CSG CEE", type any}, {"PM ProSupp Plus CSG SNS", type any}, {"PM ProSupp Plus Svr", Int64.Type}, {"PM ProSupp Plus Svr B2B", type any}, {"PM ProSupp Plus Svr CEE", type any}, {"PM ProSupp Plus Svr RTM", type any}, {"PM ProSupp Plus Svr SNS", type any}, {"PM SDS Educ Subscription", Int64.Type}, {"PM SDS ProDeploy", type number}, {"PM SDS ProDeploy CSG", Int64.Type}, {"PM SDS ProDeploy ISG", type number}, {"PM SDS ProSupport Plus", type any}, {"PM SDS ProSupport Plus CCS", Int64.Type}, {"PM SDS ProSupport Plus CSG", type any}, {"PM SDS ProSupport Plus ISG", type any}, {"PM SDS ProSupport Plus Storage", type number}, {"PM Storage New Buyer", Int64.Type}, {"PM Storage OEM", type any}, {"PM Svr Plus New Buyer", Int64.Type}, {"PM TLA", type any}, {"PM VCF AWS Rev", Int64.Type}, {"PM VMW Joint Soln", Int64.Type}, {"PM VMW Joint Soln CSG", Int64.Type}, {"PM VMW Joint Soln CSG B2B", type any}, {"PM VMW Joint Soln CSG CEE", type any}, {"PM VMW Joint Soln CSG SNS", type any}, {"PM VMW Joint Soln Svr", Int64.Type}, {"PM VMW Joint Soln Svr B2B", type any}, {"PM VMW Joint Soln Svr CEE", type any}, {"PM VMW Joint Soln Svr RTM", type any}, {"PM VMW Joint Soln Svr SNS", type any}, {"PM Workstations", Int64.Type}, {"PM Workstations B2B", type any}, {"PM Workstations CEE", type any}, {"PM Workstations Chnl", Int64.Type}, {"PM Workstations Comm PC", Int64.Type}, {"PM Workstations SNS", type any}, {"PowerStore Attainment Modifier", type number}, {"PowerStore Attainment Modifier CEE", type any}, {"PowerStore Attainment Modifier RTM", type any}, {"Pre Payout", Int64.Type}, {"PriceFloor ACQ Metric 1", Int64.Type}, {"PriceFloor ACQ Metric 2", type any}, {"PriceFloor ACQ Metric 3", type any}, {"PriceFloor Metric 1", Int64.Type}, {"PriceFloor Metric 2", Int64.Type}, {"PriceFloor Metric 3", Int64.Type}, {"SP1 CCS Rev", type any}, {"SP1 CSG Direct Rev", type any}, {"SP1 CSG Rev", type any}, {"SP1 CSG Rev Intl", type any}, {"SP1 CSG Rev w 3PSW", type any}, {"SP1 ISG Rev", type any}, {"SP1 Storage Focused Rev", type any}, {"SP1 Storage Rev", type any}, {"SP1 Svr Plus Rev", type any}, {"SP1 Svr w Ntwk Rev", type any}, {"SP1 Total Rev w 3PSW", Int64.Type}, {"SP1 Total Rev w MSFT", type any}, {"SP1 Total Revenue", Int64.Type}, {"SP2 CCS HW Rev Chnl", type any}, {"SP2 CCS Rev", Int64.Type}, {"SP2 CCS Rev Chnl", type any}, {"SP2 CCS Rev OEM", type any}, {"SP2 CCS Svcs and Upsell Rev", type any}, {"SP2 Chnl Compute Storage Rev", Int64.Type}, {"SP2 Chnl Compute Svr Rev", Int64.Type}, {"SP2 CSB CSG Rev", type any}, {"SP2 CSB ISG Rev", type any}, {"SP2 CSB TSR ISG Rev", type any}, {"SP2 CSG Direct Rev", type any}, {"SP2 CSG Rev", type number}, {"SP2 CSG Rev CEE Chnl", type any}, {"SP2 CSG Rev Chnl", Int64.Type}, {"SP2 CSG Rev Chnl w 3PSW", Int64.Type}, {"SP2 CSG Rev Chnl w T1", type any}, {"SP2 CSG Rev Chnl w T1 n 3PSW", type any}, {"SP2 CSG Rev DTS", type any}, {"SP2 CSG Rev Intl", type any}, {"SP2 CSG Rev w 3PSW", type number}, {"SP2 CSG Rev w MSFT", type any}, {"SP2 ISG Rev", Int64.Type}, {"SP2 ISG Rev Chnl", type any}, {"SP2 SPS Rev", type any}, {"SP2 SPS Rev RTM", type any}, {"SP2 Storage Focused Rev", Int64.Type}, {"SP2 Storage Rev", Int64.Type}, {"SP2 Storage Rev CEE Chnl", type any}, {"SP2 Storage Rev DTS", type any}, {"SP2 Storage Rev RTM", type any}, {"SP2 Storage Rev w Renewals", Int64.Type}, {"SP2 Svr Plus Rev", Int64.Type}, {"SP2 Svr Plus Rev CEE Chnl", type any}, {"SP2 Svr Plus Rev DTS", type any}, {"SP2 Svr Plus Rev RTM", type any}, {"SP2 Svr w Ntwk Rev", Int64.Type}, {"SP2 Total CCS Rev Chnl", type any}, {"SP2 Total CCS Rev Chnl w T1", type any}, {"SP2 Total CS Rev", type any}, {"SP2 Total Rev Chnl", Int64.Type}, {"SP2 Total Rev OEM", type any}, {"SP3 CSB Storage Rev", type any}, {"SP4 CS 3PSW Rev", type any}, {"SP4 CSB TSR ISG Rev", type any}, {"SP5 CSG Rev Chnl", type any}, {"SPS Mgn", type any}, {"SPS Mgn RTM", type any}, {"SPS New Buyer Attainment Modifier", type any}, {"SPS New Buyer Attainment Modifier RTM", type any}, {"SPS Rev", type any}, {"SPS Rev RTM", type any}, {"Storage B2B Rev", type any}, {"Storage Focused Mgn", type any}, {"Storage Focused Rev", type number}, {"Storage New Buyer Attainment Modifier", type number}, {"Storage New Buyer Attainment Modifier B2B", type any}, {"Storage New Buyer Attainment Modifier Chnl", type number}, {"Storage New Buyer Attainment Modifier Chnl CEE", type any}, {"Storage New Buyer Attainment Modifier RTM", type any}, {"Storage Rev", type number}, {"Storage Rev CEE Chnl", type any}, {"Storage Rev DTS", type any}, {"Storage Rev RTM", type any}, {"Storage Rev w Renewals", type number}, {"Storage Support Rev", type number}, {"Storage w APOS and Renewal Rev", type number}, {"Storage w APOS Rev", type any}, {"Svr Plus New Buyer Attainment Modifier", Int64.Type}, {"Svr Plus New Buyer Attainment Modifier B2B", type any}, {"Svr Plus New Buyer Attainment Modifier Chnl", Int64.Type}, {"Svr Plus New Buyer Attainment Modifier Chnl CEE", type any}, {"Svr Plus New Buyer Attainment Modifier RTM", type any}, {"Svr Plus New Buyer Attainment Modifier SNS", type any}, {"Svr Plus New Buyer Attainment Modifier SNS ST", type any}, {"Svr Plus Rev", Int64.Type}, {"Svr Plus Rev Base", Int64.Type}, {"Svr Plus Rev CEE Chnl", type any}, {"Svr Plus Rev DTS", type any}, {"Svr Plus Rev RTM", type any}, {"Svr w Ntwk Rev", Int64.Type}, {"Tech_Refresh_GA1", type any}, {"Tech_Refresh_GA2", type any}, {"Tech_Refresh_GA3", type any}, {"Tech_Refresh1", type any}, {"Tech_Refresh2", type any}, {"Tech_Refresh3", type any}, {"Total CCS Rev Chnl", type any}, {"Total CCS Rev Chnl w T1", type any}, {"Total CS Mgn", type any}, {"Total CS Rev", type any}, {"Total Deploy Rev", Int64.Type}, {"Total Deploy w Cons Mgn", type number}, {"Total Deploy w Cons Rev", Int64.Type}, {"Total Margin", type number}, {"Total Margin OEM", type any}, {"Total Margin w 3PSW", type number}, {"Total Mgn 3PSW n Ren", type any}, {"Total Mgn 3PSW n Ren Chnl", type number}, {"Total Mgn 3PSW n Ren n T1 Chnl", type any}, {"Total Mgn Chnl", type number}, {"Total Mgn Chnl w T1", type number}, {"Total Mgn DTS", type any}, {"Total Mgn w 3PSW", type number}, {"Total Mgn w MSFT", type any}, {"Total Rev Chnl", type number}, {"Total Rev Chnl w T1", type any}, {"Total Rev OEM", type any}, {"Total Rev w 3PSW", type any}, {"Total Rev w 3PSW w SP4", type any}, {"Total Rev w MSFT", type any}, {"Total Revenue", type number}, {"Total Revenue w 3PSW", type number}, {"Total SNS B2B Mgn", type any}, {"Total Supp Excl Renewals Rev", type number}, {"Total Support Rev", type number}, {"Total Svcs Mgn", type number}, {"Total Svcs Mgn Excl Renewals", type number}, {"Total Svcs SAE Mgn", type any}, {"Total Svcs SAE Rev", type any}, {"Total Svcs SSR Mgn", type any}, {"Total Svcs SSR Rev", type any}, {"Transformational Rev", type any}, {"VMC Attainment Modifier", Int64.Type}, {"VMC Attainment Modifier CEE", type any}, {"VMC Attainment Modifier RTM", type any}, {"WF SP Deploy Rev", type any}, {"WF SP Intl Rev", type any}, {"WF SP NA Rev", type any}, {"WF SP Rev", type any}, {"WF SP Rev VL", type any}, {"CALC CURRENCY CODE", type text}, {"Order Type Code", type text}, {"Split Order", type text}, {"Original Order Number", type text}, {"Original BUID", type text}, {"Original Split Percentage", type text}, {"GBL ALLNCE NAME", type any}, {"GBL ALLNCE ID", type any}, {"OEM NAME", type any}, {"OEM ID", type any}, {"END USER NAME", type text}, {"END USER ID", Int64.Type}, {"PRTNR RESLR NAME", type text}, {"PRTNR RESLR ID", Int64.Type}, {"DSTRBR NAME", type text}, {"DSTRBR ID", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Period Name"}, FY_Period, {"FY Period"}, "FY_Period", JoinKind.LeftOuter),
#"Expanded FY_Period" = Table.ExpandTableColumn(#"Merged Queries", "FY_Period", {"FY Period"}, {"FY Period"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded FY_Period", {}, "Attribute", "Value"),
#"Merged Queries1" = Table.NestedJoin(#"Unpivoted Columns", {"Attribute"}, Table5, {"METRICS NAME"}, "Table5", JoinKind.LeftOuter),
#"Expanded Table5" = Table.ExpandTableColumn(#"Merged Queries1", "Table5", {"METRICS NAME", "CLASSIFICATION", "CATEGORY", "POWERSTORE", "PERCENTAGE to LOAD", "Metric / Modfier", "Has BASE (need mod)"}, {"Table5.METRICS NAME", "Table5.CLASSIFICATION", "Table5.CATEGORY", "Table5.POWERSTORE", "Table5.PERCENTAGE to LOAD", "Table5.Metric / Modfier", "Table5.Has BASE (need mod)"})
in
#"Expanded Table5"

Thank u 🙂 

Anonymous
Not applicable

Check your applied steps in the right side of the Power Query editor and see if there's a "sort" step in there. If there is, remove the step.

KeithSayer_0-1634143122904.png

 

Also, if you are using the function labeled "Merge" I would try "Append" instead.

Hi.

I don't have any "sort" step there. I canceled all the filters and tried to merge it again and now it doesn't give me the first 3 data below itself in the form: Badge, Badge, Badge, Name, Name, Name .. but it only gives the first 2 - Badge, Badge, Name, Name. I can't use the append because I need it to merge common values ​​for me - that is, to append values ​​from one table to another based on an ID.

But thank u for trying 🙂 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors