Converting single rows into multiple duplicate rows w/multiple columns
Need to convert data for a single person on one row with multiple columns into multiple rows for the same person plus each column of related data. Transpose does not work. Power Query might do the trick, but this is the first time using that tool. Any help is much appreciated! Here is the sample data to start with: Convert to:
Power Query freezes in Excel
Hello, I'm having issues editing queries in Excel. Whenever I enter a Power Query window, the window opens but shows no data and all excel sheets freezes. I can highlight the tools but they do not react when I try to click them. Nor can I close the Power Query window. The only way I manage to close excel is by forcing it through task manager in windows. I believe the problem started when I updated excel from verision 1902 to 1907. I have reinstalled office and I am now running version 1908, but the problem is still present. I have tried to access power query in multiple workbooks, but with no success. Has anyone experienced a similar problem? Any suggestions on how to fix this would be very welcome. Thank you for your time!
Date value changes to text on refresh of Power Query data - only for Excel 365
I have an issue where my date columns are transforming to numbers and defined as 'general' when I refresh the data. I've tried formatting the entire column as date and saving the file. The next time I open the report and refresh the dates are converted to it's numerical equivalent. In Power Query, the columns are seen as Date/Time; therefore, I changed it to 'Date' to see if the Time portion could be causing a problem. That did not help. I'm at a loss as to what the issue is. This does not happen with Excel 2013. I can't find any settings in Excel 365 that might be causing this.
Create dynamic Power Query SQL by using Excel table column?
Is it possible to include the contents of an entire table column within the SQL code in Power Query? Background I created a spreadsheet to maintain an inventory of devices by serial number (sn). The inventory is in an Excel table. Separately, I use Power Query to connect to an external DB (Redshift) in order to return data only about the sn items I supply. A simplified version of the SQL I use is: select sn, data2 from table1 where date_key BETWEEN '2018-01-01' AND GETDATE() and ( sn = 'AA001' OR sn = 'AA006' OR sn = 'AA012' OR sn = 'AA025' ) When the list of sn's change, I need to paste the new list into the SQL. To simplify things (because my list is hundreds of items long) I created a second table using Power Query to build the list of the exact sn's I need based on various filters. Once the table is built by Power Query in Excel, I then use the following formula to add the proper syntax to the list of serial numbers, which also excludes the last "OR" statement: ="sn = '"&[sn]&IF(INDIRECT("A" & ROW() + 1)="","'","' OR") and creates the "Query" column as shown below based on the input from "sn" sn Query AA001 sn = 'AA001' OR AA006 sn = 'AA006' OR AA012 sn = 'AA012' OR AA025 sn = 'AA025' I can now cut/paste the "Query" column into Power Query to update my list of serial numbers. While I've automated this quite a lot, there is one last thing I'd like it to do: Question Can I somehow reference the "Query" column in my SQL statement so that I no longer need to directly update the SQL statement as new devices are added or removed from the device inventory? Since the Query column contains everything I need for the data between the parenthesis in Power Query, referencing it somehow should allow the SQL to become dynamic. Is this possible? Or is there an even better way to do what I'm doing? Thanks!Power Query: Enable "Use fuzzy matching to perform the merge" Option
Hello, I am using Excel 2016 version with the Fuzzy Lookup Add in already installed. For some reasons, I can not see the option "Use fuzzy matching to perform the merge" under the Join Kind"'s Merge field, at the Power Query Merge to join two tables. Please, let me know how I can enable this Fuzzy merging in the Powerquery. Thank you, Sala.
