Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Thank you very much.
Solved! Go to Solution.
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 🙂
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 🙂
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 ;). |
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. | Proud to be a Super User! |
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 🙂
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.
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 🙂