I have 3 tables which are extracts from 3 systems - the first 3 columns are A) ID (different for each system), B) first name & C) surname - the following columns are activities, some unique to one table, others common between two tables - the cells are populated with a date by person/by activity - where the activity is common between two tables, the date is typically different (but could be the same) - people may exist on 1, 2 or 3 tables ... I hope that makes sense? What I want to do is create a table that i) has every person, ii) has every activity, iii) has the latest date by person/by activity (or blank where no date exists for that combination) ... I'm going round and round thinking this through and am hoping someone can set me off in the right direction? Many thanks ... A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data. Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question. Anonymous 2019-11-25T17:12:46+00:00 PowerQuery. You can create separate queries to retrieve the data from the 3 separate databases. Perform what ever data cleaning magic you need to to get the data into a consistent format, then load it all into the final merged data table. Since you don't give us very specific information, here is some general information about the feature and how it may relate to your situation So what is Power Query? https://www.myexcelonline.com/blog/50-things-you-can-do-with-excel-power-query/ Well you know when you get data from a file, a data dump/import or an external data source and it comes into Excel in an ugly, unworkable format? I can hear you say YEEEES! Well Power Query accesses, cleans and transforms all that messy data and displays it in a way that Excel loves and can work with. The best thing is that the next time you receive the same ugly file, all you need to do is press the Refresh button and your data will automatically be imported, cleaned up and loaded into Excel so the data is displayed the “correct” way! So no more formulas, text to columns, trim spaces, vlookup, find & replace etc. You have all that functionality in Power Query at the press of a menu command! ! The Complete Guide to Power Query 2018 02 11 https://www.howtoexcel.org/power-query/the-complete-guide-to-power-query/ Power Query is a business intelligence tool available in Excel that allows you to import data from many different sources and then clean, transform and reshape your data as needed. It allows you to set up a query once and then reuse it with a simple refresh. It’s also pretty powerful. Power Query can import and clean millions of rows into the data model for analysis after. The user interface is intuitive and well laid out so it’s really easy to pick up. It’s an incredibly short learning curve when compared to other Excel tools like formulas or VBA. The best part about it, is you don’t need to learn or use any code to do any of it. The power query editor records all your transformations step by step and converts them into the M code for you, similar to how the Macro recorder with VBA. ! a Microsoft Power Query for Excel Help (in wiki) https://support.office.com/en-us/article/microsoft-power-query-for-excel-help-2b433a85-ddfb-420b-9cda-fe0e60b82a94 This is MS home page for PowerQuery help, with links to MANY detailed help pages Power Query provides data discovery, data transformation and enrichment for the desktop to the cloud. Easily discover, combine, and refine data for better analysis in Excel. . * Introduction to Microsoft Power Query for Excel . * Import data from external data sources . * Shape data from multiple data sources @**** (Over 80) External Data sources available in Power Query. (in Wiki) **** https://d13ot9o61jdzpp.cloudfront.net/images/pq_get_data.png https://www.myonlinetraininghub.com/power-query-and-power-pivot-definitive-guide If you attended my Excel Dashboard webinar with Power Query and Power Pivot this past week, you’ll have seen how easy it is to build interactive dashboard reports with these amazing new tools. ET PT PivotTables Power Query BI.docx @ What is Power BI, Power Query and PowerPivot https://chandoo.org/wp/what-is-power-bi-power-query-and-power-pivot/ @ Import and analyze data https://support.office.com/en-us/article/Import-and-analyze-data-ccd3c4a6-272f-4c97-afbb-d3f27407fcde#ID0EAABAAA=Import_data You can use Excel’s Get & Transform (Power Query) experience to connect to multiple data sources, then use the Query Editor to shape and transform data. Getting Started with Get and Transform Import data from external data sources View and Manage Queries in a Workbook Add a query to an Excel worksheet Introduction to the Query Editor Shape data Edit Query Step Settings Col- **Merge Columns** (6min28) March 22, 2017 https://ozdusoleil.com/2017/03/22/power-query-merge-columns/ More about working with Excel’s Get & Transform (aka Power Query). this time, we explore merging columns (horizontally) and the “gotchas” that you need to know about. 0:49 Description of the 3 warnings 1:09 Showing the problem 2:00 Problem 1: The source columns are gone 2:17 Problem 2: The columns are out of order 2:50 Merge properly 3:09 Problem 3: Excess delimiters 3:29 A hack for replacing the excess delimiters 5:12 Using TEXTJOIN to do the work in native Excel join- Using Power Query to Connect Tables for Reporting - Excel.TV 2019 05 14 https://www.youtube.com/watch?v=c1hJM4-TS0I (6min50) I show you how to connect two tables via Power Query. It's really easy. Last week, we used a formulaic method, which, I have to admit, was not the best way to solve the problem. You can watch last week's video by following this link: https://www.youtube.com/watch?v=wSyiW... 0 comments No comments Report You can create separate queries to retrieve the data from the 3 separate databases. Perform what ever data cleaning magic you need to to get the data into a consistent format, then load it all into the final merged data table. Since you don't give us very specific information, here is some general information about the feature and how it may relate to your situation So what is Power Query? https://www.myexcelonline.com/blog/50-things-you-can-do-with-excel-power-query/ Well you know when you get data from a file, a data dump/import or an external data source and it comes into Excel in an ugly, unworkable format? I can hear you say YEEEES! Well Power Query accesses, cleans and transforms all that messy data and displays it in a way that Excel loves and can work with. The best thing is that the next time you receive the same ugly file, all you need to do is press the Refresh button and your data will automatically be imported, cleaned up and loaded into Excel so the data is displayed the “correct” way! So no more formulas, text to columns, trim spaces, vlookup, find & replace etc. You have all that functionality in Power Query at the press of a menu command! ! The Complete Guide to Power Query 2018 02 11 https://www.howtoexcel.org/power-query/the-complete-guide-to-power-query/ Power Query is a business intelligence tool available in Excel that allows you to import data from many different sources and then clean, transform and reshape your data as needed. It allows you to set up a query once and then reuse it with a simple refresh. It’s also pretty powerful. Power Query can import and clean millions of rows into the data model for analysis after. The user interface is intuitive and well laid out so it’s really easy to pick up. It’s an incredibly short learning curve when compared to other Excel tools like formulas or VBA. The best part about it, is you don’t need to learn or use any code to do any of it. The power query editor records all your transformations step by step and converts them into the M code for you, similar to how the Macro recorder with VBA. ! a Microsoft Power Query for Excel Help (in wiki) https://support.office.com/en-us/article/microsoft-power-query-for-excel-help-2b433a85-ddfb-420b-9cda-fe0e60b82a94 This is MS home page for PowerQuery help, with links to MANY detailed help pages Power Query provides data discovery, data transformation and enrichment for the desktop to the cloud. Easily discover, combine, and refine data for better analysis in Excel. . * Introduction to Microsoft Power Query for Excel . * Import data from external data sources . * Shape data from multiple data sources @**** (Over 80) External Data sources available in Power Query. (in Wiki) **** https://d13ot9o61jdzpp.cloudfront.net/images/pq_get_data.png https://www.myonlinetraininghub.com/power-query-and-power-pivot-definitive-guide If you attended my Excel Dashboard webinar with Power Query and Power Pivot this past week, you’ll have seen how easy it is to build interactive dashboard reports with these amazing new tools. ET PT PivotTables Power Query BI.docx @ What is Power BI, Power Query and PowerPivot https://chandoo.org/wp/what-is-power-bi-power-query-and-power-pivot/

@ Import and analyze data https://support.office.com/en-us/article/Import-and-analyze-data-ccd3c4a6-272f-4c97-afbb-d3f27407fcde#ID0EAABAAA=Import_data You can use Excel’s Get & Transform (Power Query) experience to connect to multiple data sources, then use the Query Editor to shape and transform data. Getting Started with Get and Transform Import data from external data sources View and Manage Queries in a Workbook Add a query to an Excel worksheet Introduction to the Query Editor Shape data Edit Query Step Settings Col- **Merge Columns** (6min28) March 22, 2017 https://ozdusoleil.com/2017/03/22/power-query-merge-columns/ More about working with Excel’s Get & Transform (aka Power Query). this time, we explore merging columns (horizontally) and the “gotchas” that you need to know about. 0:49 Description of the 3 warnings 1:09 Showing the problem 2:00 Problem 1: The source columns are gone 2:17 Problem 2: The columns are out of order 2:50 Merge properly 3:09 Problem 3: Excess delimiters 3:29 A hack for replacing the excess delimiters 5:12 Using TEXTJOIN to do the work in native Excel join- Using Power Query to Connect Tables for Reporting - Excel.TV 2019 05 14 https://www.youtube.com/watch?v=c1hJM4-TS0I (6min50) I show you how to connect two tables via Power Query. It's really easy. Last week, we used a formulaic method, which, I have to admit, was not the best way to solve the problem. You can watch last week's video by following this link: https://www.youtube.com/watch?v=wSyiW... Anonymous 2019-12-16T05:16:37+00:00 The key is the date, You must create a calendar table that accounts for every date possible. for example if you are pulling dataset from the 3 systems with a date range of three years, then your calendar table must cover all dates for all the possible dates of all 3 years. then you can append all data from the 3 systems, list all the user fields into rows and all the dates into columns. your question really is how can you display a blank activity on a blank date. the calendar table with all possible dates in a range of time/years would be your solution. 0 comments No comments Report The key is the date, You must create a calendar table that accounts for every date possible. for example if you are pulling dataset from the 3 systems with a date range of three years, then your calendar table must cover all dates for all the possible dates of all 3 years. then you can append all data from the 3 systems, list all the user fields into rows and all the dates into columns. your question really is how can you display a blank activity on a blank date. the calendar table with all possible dates in a range of time/years would be your solution. Anonymous 2019-11-29T02:21:05+00:00 porv que o welss wordv e pore pont estão pedindo uma chave de acesso 0 comments No comments Report porv que o welss wordv e pore pont estão pedindo uma chave de acesso Ashish Mathur • 102.3K Reputation points • Volunteer Moderator 2019-11-25T23:34:58+00:00 Hi, Share some data and show the expected result. 0 comments No comments Report Share some data and show the expected result. Anonymous 2019-11-25T16:30:21+00:00 Hi, AdamBC, I'm Mitch, a Microsoft user like you. You can consolidate the 1st 3 columns, concatenate and remove duplicates. Add columns based on the data you need from your 3 tables and lookup the values with the merged data from the 1st 3 columns as your criteria. 0 comments No comments Report Hi, AdamBC, I'm Mitch, a Microsoft user like you. You can consolidate the 1st 3 columns, concatenate and remove duplicates. Add columns based on the data you need from your 3 tables and lookup the values with the merged data from the 1st 3 columns as your criteria.