How to convert JSON data into Excel table in Power Query
I would like to import the JSON file into Excel, I am using Power Query as I'm not versed in VBA. I would like the output to look like this: The issue I'm having is figuring out how to to transform the data based on my data I have. Below is sample data: { "items": [ { "tableName": "tableA", "count": 1729, "columnNames": [ "id", "createdTime", "updatedTime", "name", "category", "id2", "subject", "text" ], "rows": [ [ "1234567", "2019-02-02T14:54:02Z", "2019-02-02T20:57:32Z", "John Smith", "123", "1234", "Product A - need assistance ", "text field" ], [ "2345678", "2019-02-03T14:54:02Z", "2019-02-03T20:57:32Z", "John Smith", "123", "1234", "Product B help", "Text field" ], etc } The first step was converting the json file to a table, afterwards, I expanded 'value' cell to new rows and did so another time, to get thisimage: From there I further expanded and to get all the values. I tried removing duplicates and transpose but those options are leading to the tabular data I am looking for. How do I go about solving this issue? Thanks for your time.
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!
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.
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 [DataFormat.error] External table not in expected format
Hi all, I have been working with Power Query as a way to combine datasets from two systems then plugging it into PowerBI for about a year now. This month, when I ran and refreshed data for 2018 year-end, I am getting an error I have never gotten before: There are always a few things to tweak here or there like adjusting the files that have been added to the source (SharePoint) so that it narrows down to the correct file. That is not the problem here. It seems to only error out at the step where the table is expanded, before any of the transformation steps. I have gone through my source files and checked their columns for data type and they're all normal. A few of the tables weren't updated at all, however, I understand that if one file errors out - all subsequent files will error the same way. Is there a way to find out which file is causing it? I'm not sure where in the grand scheme of things I should look for file order. Any insight would be immensely helpful! Note: the very first file and query are not getting an error. However, there are a jumble of non-linear queries and files from there on out (i.e., file #2 in SharePoint doesn't match query #2 in Power Query - which one matters here?)
