Trouble loading JSON to Excel
I have a JSON file that I would like to import into Excel I have Office 365 I am using the following: ​Use Excel's Get & Transform (Power Query) experience to connect to a JSON file. Click the Data tab, then Get Data > From File > From JSON. Browse to your JSON file location, select it, and click Open. Once the Query Editor has loaded your data, click Convert > Into Table, then Close & Load. I only receive two rows in my excel spreadsheet but there is quite a bit of data. Help appreciated. -JPcalculating water rates on a per thousand/tier
I am trying to calculate a water table that I could change the rates in B12-B23 in order to get a monthly bill about for water consumption. I have found several issues. Even if 0 qty is used there still is a charge of 7.50 I need to know what to put in the formula to have 7.50 put in even if 0 qty of water is used. Qtys 0-3 do not have a monthly 7.50 charge so all other amounts above that need to have 7.50 added to the bill. I need my equation to charge the monthly fee if applicable, and the cost per thousand. example in B13 the qty used was 2 (2000 gallons of water) that water is charged at 7.50 flat. Because 0-3 qty has a flat rate of 7.50. But on B14 the formula should show that the customer is being billed 7.50 for the 1st 3k gallons of water, and then needs to be billed 3.19 for the extra thousand gallons of water. Each thousand gallons of water used needs to be billed based on the water sales. Does this make sense? attached is my spread sheet. Please HELP!!
Power Query adds an apostrophe to cells queried from an Excel table
Excel 2010, Power Query version 2.48.4792.941 32-bit. I have a table (call it Table_Source) in an Excel file (call it source.xlsm) that I query from another Excel file (call it destination.xlsm). The source table contains multiple columns, some formatted as text, some formatted as general. In destination.xlsm, I use Power Query to query Table_Source. Some of the queried columns are written into destination.xlsm with a leading apostrophe in each cell, even though the columns are formatted as text. This wreaks havoc with some VBA code. How do I get Power Query to not append the leading apostrophe? Thank you!
Data analysis function not working
Good afternoon, I am using Excel 2013. I have added in the Data Analysis add-on (and the VBA one). The data analysis button shows up on the right of my toolbar, but when I click it nothing happens..the box with the options of which test to run does not pop up anywhere (and I've looked to see if it's hidden behind another pane, etc.). I've tried un-installing the add on and re-installing it, Restarting my computer, etc...nothing has worked thus far. Any other suggestions?Issues with Pivot table: Data Model is not applying relationship
Hi there, What I'm trying to do is build a pivot table that utilizes a data model. Unfortunately, for some reason, the relationships described in the data model are not being applied to the pivot table. I'm not sure what I'm doing wrong. The table structure is very simple. My Tables: Data Relationship Pivot Table My Expected Results are: James Corbin: BA Jim Jam: PM Lara Croft: Java, BA, PM Row Low: Java, .NET What am I doing wrong? Thanks in advance.
Reading .csv stored in Azure Blob Storage from Excel
I've been able to create a storage account, then a container, than a blob storing a .csv file. I've successfully been able to download the .csv file from Azure itself and using Python as well via the azure-storage package. This is to confirm that external sources (i.e. Python) are able to import/export blobs. The .csv stores a numeric table with header in the first row. The second step is to import the same data in Excel 2016. The steps that I'm following from Excel are: New Query --> From Azure --> From Microsoft Azure Blob Storage --> provide <Account_name> and <Key> --> Navigator. From here, I can see that dataset.csv file in the container. However, clicking the button "load" in the Navigator only allows to download the table in the previous picture in excel, and not the data stored in those files. I've explored the add ons "Design" and "Query" to look for a button to download the data stored in those blobs but unsuccessfully. Question: how to import the numberic data stored in one of the .csv in Azure Blobs listed in the Wizard?
