Home

Get and Transform Data

123 Conversations

Latest Activity

Custom List Message Item

Curious if this is a known issue. I'm trying to pull in data into power query using a web URL, however, i'm only getting 24,998 rows of a total 278K. When pulling the data in using Get external data vs. Get & transform, all 278K rows are loaded. This also

... Read More
23 Views
0 Reply

Hi all,

 

I have a question related to usage scenarios of the legacy Import from Web connector in Excel.

 1 - WebConnector.jpg

 

Today the legacy Import from Web connector supports the following features, which I’d like to get your perspective on:

  1. Importing the entire page.
... Read More
748 Views
11 Replies

Very important, the new 2016 method breaks a lot of web connections for me.  For example, I have a page that I cannot get to without entering a login.  Old way the page o

... Read More

I am a developer for an application which relies upon this for key reporting functionality.

 

It's only now, when trying to find out why it's broken, that I find out that t

... Read More

My exerience lately has been trying to help someone with the legacy connector because the target web page changed. After a couple rounds of click-here, try-this, I ask if

... Read More

Hi Guy,

 

Even though you only had one response up till now, I'd like to stress that that doesn't mean this feature isn't used much. I have a couple of customers that rely

... Read More
That's only my own experience - for many years used this feature quite seldom. These days Power Query connector to Web works fine for my purposes, even forgot about above... Read More

I am using MS Exchange to 'Get & Transform Data' from a MS Exchange Account. The method of linking and extraction works fine. The problem is that Excel is only loading the first 250(ish) characters of the message body from each mail. I cannot find a way t

... Read More
43 Views
0 Reply

Power Query Premium: All Power Query features available for: Professional Plus, Office 365 ProPlus or Excel Standalone
Power Query Public: Available for all other Office D

... Read More
Best Response confirmed by Riccardo Lavoro (Visitor)

I've got the same issue: I've followed the instructions in here but can't see the MySQL Connection in the  New Query - from DB Dropdown

I'm working with MS Excel 2016 sta

... Read More

I have the same issue as the gentleman from Ratheon.  I have built a file that successfully queries a client's Sql database, and now want to give that file to one of their accounting folks to refresh and use on a daily basis, without my having to do it fo

... Read More
110 Views
6 Replies

In case you do not know, I have learned something unfortunate about Power Query.

When you create a data connection to a database, the tool stores the credentials (see Dat

... Read More
It is curious why there is no answer for this. Perhaps it is a defect. However, the tool has been around for 7 years. Not sure why this has not been addressed. The da... Read More

I am getting data from Multiple Web pages into multiple excel worksheets. Each Web page has the same format, and the same changes need to be made to each worksheet using the Query Editor before I load the data into Excel.

 

Is there a way I can replicate

... Read More
76 Views
6 Replies

Hi Gurpreet,

 

You may create the query for one web page, after that duplicate it from the menu (right click on the query) and use for another web page. Connection string

... Read More

When I create a power Query with a sql server data connection in the current workbook and then give the workbook to someone else, they are prompted for the credentials.  Why is this happening and how do I prevent it?

73 Views
1 Reply

In case you do not know, I have learned something unfortunate about Power Query.

When you create a data connection to a database, the tool stores the credentials (see Dat

... Read More
Best Response confirmed by Daniel Levite (New Contributor)

Hello!

 

I am trying to set up my workbook so that information from the master page mirrors to a secondary page, based on specific criteria. (So I don't have to manually copy each column, and information changes on the master page reflect on the secondary p

... Read More
64 Views
1 Reply

I am very sorry about the first reply to you.

 

May I know whether my suggestion or Sergei Baklan's suggestion fit your need?  I think Sergei Baklan has provided a better

... Read More

There is allegedly an option to import JSON data in XL 2016, but the option is missing in my copy. It isn't there in Customize the ribbon, so it can't be added from there, either. Any ideas where it got to?
 

XL2016-JSON-option-missing.png

Read More
2,670 Views
5 Replies

There is an easy workaround. Go to Data Tab on Ribbon and follow these steps:

 

1. New Query -> From Other Sources -> From Web;

2. Type in (or Copy-Paste) an url to you J

... Read More

I don't have an Office 365 suscription.

I used "From Text" intead of "From JSON". Then select All filetypes, and you can open de JSON file. Hope it works for you too.

 

Rega

... Read More

Hi Noel,

 

I could be wrong but JSON data source was added not a long ago. If except insiders all new functionality goes first to O365 subscribers, and one day in future to

... Read More

Hi there,

I have a simple problem that I'm sure has a simple solution but my lacking skills are blocking me from accomplishing what I'd like to do. I;m good at generating data but not manipulating it.

 

I have many tasks at work but by far the least skill

... Read More
40 Views
0 Reply

I have used Excel for 25 years; Office and Excel for Mac for 8 years.  With the increased need for real-time/relevant data, I decided to replace the old 'copy/paste' HTML data with a more efficient process and turned to YouTube videos to learn how whereup

... Read More
33 Views
0 Reply

Is it possible to get excel to return a cell value if another cell meets my criteria, many times over varying spaces?

 

I have a list that might look like this (only muuuch longer)

 

1000

 

11000

11001

11002

11003

 

1100

 

23000

11000

23001

 

1200

 

23003

 

1250

 

23001

32501

 

I

... Read More
203 Views
16 Replies

Hi Sheena,

 

Perhaps the easiest way is to use Power Query (Get&Transform in Excel 2016), that looks like

Numbers4-5.JPG

 

and the script generated from user interface is

let
    Source = Ex
... Read More

Just working on a simple Excel task....

 

Hello, I often have to correct data in excel and there is a feature in excel that is interfering with my work. I'm sure that other people that do any kind of data entry have come across this feature and are simply

... Read More
77 Views
7 Replies

Hi Mel,

 

More exactly it happens when you double click the border around the selected cell. Double click on the cell (within the border) shifts you into cell edit mode.

... Read More

I am looking to use the Get & Transform feature within excel to link into a Teradata database and then directly write queries against that.  When I go to link to the server, I keep getting the following error message saying more software is needed to link

... Read More
25 Views
2 Replies

Note: Novice User, using Excel 2013.

 

I want to create a non-repeating list of possible combinations/permutations from a single column table.

 

I want to make a simple table called "letters". The number of items will vary. I'm using alphabet characters in th

... Read More
51 Views
2 Replies

This is not possible in Excel??

Hello

 

LInk : http://rajasthanindustries.org/ViewCompanyProfile.aspx?id=All&typet=alpha

 

There is a dataset which I want to get and transform using web, its in table but not able to identify , any trick to know which table has large data set 

 

Please suggest

Read More
260 Views
4 Replies

Problem Solved !!!

 

Thanks for the hint 

 

Please find the code to solve this problem 

 

let
Source = Table.FromColumns({Lines.FromBinary(Web.Contents("http://rajasthanindustries.org/ViewCompanyProfile.aspx?id=All&typet=alpha

... Read More

Hi Sachin,

 

I am not an expert in websites, however, as far as I have heard, if 'Document' is the only item you see in the 'Navigator' menu when connecting to a website (s

... Read More

Problem solved

 

Created a table on sheet1 with name youtube

 

YoutubeString
PlaylistPLojk56IbG34w6q67wt_JQjgITJPdwtto8
Key[mykey]
Urlhttps://www.googleapis.com/youtube/v3/playlistItems?part=snippet&maxResults=50&playlistId=PLojk56IbG34w6q67wt_JQjgITJPdwtto8&key=[mykey]&pageToken=

... Read More
Best Response confirmed by Sachin Jain (Occasional Contributor)

Can any one illustrate one example of using Oauth2  for Get and Post Rest API

 

I am looking for Youtube api using Oauth2 , I am able to work with API key but not with Oauth2. Youtube provide oauth json,

55 Views
0 Reply

Hi Everyone,

 

I am importing data through a CSV query. This query updates the two left columns in a single table, shown in Fig. 1 as the two large black boxes (Global PMT Name and Subsystem). The 'x' marks to the right are within the drawn table but are no

... Read More
35 Views
1 Reply

John,

 

the columns with the 'x' are not connected to the adjacent cells to the left. So this will never work

However, I've stumbled across something called "self-referencin

... Read More

When using a drop down box, is there a way to allow data entry for items not in the existing dd box

45 Views
2 Replies

I tend to use this option, as it prompts the user with a warning and allows them to proceed

 

Validation.PNG

Read More

I have a txt file (query1.txt) saved in my Excel Queries folder that contains the following text (it's displaying as a link in this forum, but in my plain txt file it's just the text):

https://api.quadrigacx.com/v2/ticker?book=btc_cad

 

 

 

(It's followed by a

... Read More
58 Views
0 Reply

Hi, I am trying to import a text file, basically its a XYZ file, having rows of 9403352. When I import data in a worksheet, it imports the data till 1048576 rows. A warning message also comes that "....You can import the rest of data by repeating the impo

... Read More
312 Views
4 Replies

Hi Bhanu,

 

If you use legacy Text Import Wizard on the first step you see an option from which row to start the import - put here one next to already imported.

StartImport.JPG

Other words,

... Read More

I have a table with a buget for works in a house like the one bellow. There is one column used to all descriptions, incluinding grouping items like this:

 

Article Description        Value

1.         Sitting Room   

1.1       Painting

1.1.1    Wall            

... Read More
249 Views
17 Replies

Hi! 

Please have a look with this. 

And please don't forget to comment.

 

Thanks in advance.

JBC$61892

Read More

Hi Joana,

 

Perhaps there is more clever solution, but i did that in straigforward way - split Article on levels and fill each level by it names. Steps are in attached file

... Read More
Best Response confirmed by Joana Villas-Boas (Occasional Contributor)

Hi, I want to import a lot of data to my worksheet in Excel from a website. As i tried doing it a time ago I found a button under the tab "data" which said "from web". But now when I try to do the same on my Macbook with Excel 2016 in Office 365 Education

... Read More
61 Views
0 Reply

Hi,

In the query editor I can load a "column 1" from "table 1" with this formula :

= Excel.CurrentWorkbook(){[Name="table 1"]}[Content][Column 1].

But is it a way to select more than 1 column ?

44 Views
2 Replies

Hi Kriss,

 

That could be like

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][[Column1],[Column2]]
in
    Source

 

Read More
Best Response confirmed by Kriss aKa Lallou (New Contributor)