Home

Get and Transform Data

151 Conversations

Latest Activity

Custom List Message Item

Hi everyone,

I'm using Get and Transform Data to get data from my webapi. If the amount of data is big and the job is hard, the webapi can take few minutes to response Excel. The problem is about 1m30s after the begin, Excel launch a new query to my webap

... Read More
25 Views
1 Reply

Ok I found a the solution. The first time I create the query, I can set the timeout in advanced settings window.

Excel.png

 

For an existing query, I can add the timeout with the

... Read More
Best Response confirmed by Geoffrey Estrade (New Contributor)

How does one link Excel data (just 6-8 fields) to automatically fill columns in a Sharepoint list when the document is uploaded.  Is a connection library needed?

231 Views
6 Replies

Hello,

 

you can create a SharePoint list from data in an Excel workbook, but after the list has been created, there is no further connection between the workbook and the l

... Read More

Problem: I want to create a list of all accounts that have ordered 1-3 pallets in the last 12 months of Product A  but have also ordered 20 or more pallets of Product B in the last twelve months.   What is the best way to solve this issue?

Read More
86 Views
10 Replies

I created a dataset with 4 fields [Accounts], [Dates], [Product A] and [Product B]
and I applied the transformation below using "Get & Transform" (Power Query).

Not sure i

... Read More
Can you share some sample data please?

Hi friends,

 

We have recently announced the January 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in.

 

In this update, we have released the following features:

  • New OLE DB connector.
  • Enhanced “Combine Binaries” experience when i
... Read More
489 Views
7 Replies

In the old tool for importing data from a CSV-file it was easy to select a column with a JMD date in it (eg totday: 180208) and convert this into an actual date in Excel.

... Read More

I have been working on the G&T January 2018 updates and they are really good. 

Creating buckets or bin ranges such as 0 to 50 ... 50 to 100 is a real time saver and it wo

... Read More

They say :

 

"These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have

... Read More

I am a G&T novice and just wondering if it is possible to use this function to split a file into two separate datasets.

 

I have a .txt file which has a set of headers at the top followed by data in rows.  Then at some point half way through the file ther

... Read More
68 Views
4 Replies

Hi Huge,

 

Yes, the idea is to define where is the row with your second header within the source table. You may add Index column, select the row with second header, defin

... Read More
Best Response confirmed by Hugh Barry (New Contributor)

Hey there,

I am currently trying to connect my Excel 2016 (32-bit) instance to a postgreSQL database in order to import data from the database.

I tried to follow the steps described in the tutorial of Excel2016 :https://support.office.com/de-de/article/herstellen-einer-verbindung-mit-einer-postgresql-datenbank-power-query-bf941e52-066f-4911-a41f-2493c39e69e4?ui=de-DE&rs=de-DE&ad=DE

... Read More
23 Views
0 Reply

Hey there,

I am currently trying to connect my Excel 2016 (32-bit) instance to a postgreSQL database in order to import data from the database.

I tried to follow the steps described in the tutorial of Excel2016 :https://support.office.com/de-de/article/herstellen-einer-verbindung-mit-einer-postgresql-datenbank-power-query-bf941e52-066f-4911-a41f-2493c39e69e4?ui=de-DE&rs=de-DE&ad=DE

... Read More
21 Views
0 Reply

I have a query - and I am happy to implement this in the data model as that is where it is loaded to - so either in a custom column in the query tool or a column added in the data model, I don't care either way - but it need to be in load to data model on

... Read More
31 Views
1 Reply

You can do it in-place using  fill -> down (under any column) or duplicate the column and then use fill -> down 

I'm cleaning up a gigantic (20000+) Excel list of a salesperson's contacts with 20+ columns of info about the contact, and while it has a lot of duplicate people in it, they often have new information in the row that would be lost if I just removed duplic

... Read More
55 Views
3 Replies
But what if the "duplicate" row actually contains information that supersedes the existing information, like a change in address?

Hi Guys,

So I have a question regarding the Power Query tool (2013)/ Get and Transform Data (2016). I'm using the 2013 version myself but I don't think that matters.

What I ultimately want is to create a table in excel where every time-interval, preferabl

... Read More
33 Views
0 Reply

Hello,

 

I need to combine columns, but I only want one value per column. 
 -If one column is empty, I want the value of the other in the new column.

-If both columns are different I want one column to have priority, in the example Column A.
-If both column

... Read More
51 Views
2 Replies

Hi Alexander,

 

Do you mean

=IF(LEN(A1)=0,B1,A1)

?

Best Response confirmed by Alexander Vrints (New Contributor)

I have six sheets with a history of the past 3 months of prices on 6 cryprocurrencies. I pulled the data for each sheet using the Power Query on Coinmartketcap.com  Example: https://coinmarketcap.com/currencies/tron/historical-data/?start=20171102&end=20180202

... Read More
41 Views
1 Reply

How about using a parameter (type text) for start date ("20171102") and end date ("20180202") and use these parameters in the source step of your queries? Replace the har

... Read More

I am following this article and Get Data From Azure is not listed as an option for me.  I am running Excel 2016 Build 8911.2016 Business Premium.  What am I missing?  How can I import Azure storage tables into Excel.  Works fine in Microsoft Power BI Desk

... Read More
61 Views
1 Reply

Hi Michael,

 

Advanced analytical features are not part of Business Premium (e.g. you have no Power Pivot). Similar for PQ functionality https://support.office.com/en-us/article/Where-is-Get-Transform-Power-Query-e9332067-8e49-46fc-97ff-f2e1bfa0cb16?ui=en-US&rs=en-US&ad=US

Read More

Hello all, I am new to these forums so forgive if I am doing anything wrong.

I have come across a problem with Power Query in Excel 2013.  I am importing from a Table in a different Excel workbook, I have removed some columns and i then want to sort the da

... Read More
1,255 Views
2 Replies

Hi Peter,

 

That could be bit abstract without knowing the details of you model, however,

- on PQ site try to isolate your table in memory using 'Add this data to Data Model

... Read More

Hello, I am struggling to make it work.

I have a sheet opened, as a summary of other sheets, and I need to get exactly one cell of each worksheet.

This data is in another location, but also in the same OneDrive directory, and I want to make it available for

... Read More
44 Views
1 Reply

Sergio,

 

I think that the better solution is what described in the support article!

You aren't bound to upload data into a new worksheet, you can load them into an exist

... Read More

Hello,

 

Has any one meet the improper txt data import after updated Data import wizard ?

The problem is when I have trying to import txt file data numbers with comma. For example if I try to import txt file with record : Adam 10,2. Result of inserted rec

... Read More
54 Views
1 Reply
Solved. Data type detection doesn't right recognize number with ",". Can be solved by option "Do not detect data type".

Hello

 

I am trying to concatenate text while being able to keep some of it bolded, underlined, etc.

 

e.g.

 

Cell A1 = "Hello"

Cell B1 = "Folks"

 

And I want to have as output in cell C1 = "Hello Folks"

 

How do I do it?

Read More
70 Views
4 Replies

Your question seems easy at first glance!
But it's difficult to even with the VBA!

 

Anyway, there are some fairly good solutions here.

Read More

Are there any details on what will happen to the data if I use Get & Transform to bring in a database table, and then add another column to put in my own metadata? If I comment on a row, but it's deleted in the data source, is the row preserved with my co

... Read More
30 Views
0 Reply
Hi, I just started exploring VBA, Macro, and etc. There is a folder on our server that purchase orders are saved in as their number and type. I.E. 25000-S, 25001-J, 25002-R, and so forth. I have tried to get data through query but it brings them in
with bi... Read More
37 Views
0 Reply

Hi all,

 

I'm on the Mac version of Excel 2016 and am wondering how to connect two workbooks (as in link a complete sheet to another workbook, not just referencing specific cells), because the instructions on the Office Support site don't seem to match what

... Read More
206 Views
2 Replies

Idem for me ! 

 

 

 

I'm facing the same issue.

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
441 Views
1 Reply
Well, if that data is displayed in cell A1, you can display the data you need (9329.88) by going to the cell where you want to place the data, then using this formula: =l... Read More

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 (Frequent 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

 

Some may see Get & Transform (Power Query) simply as a brilliant way of connecting to multiple data sources and loading that data into a Microsoft Power BI development environment (Excel Power Pivot or Power BI desktop).

 

Having spent 25 years using Excel

... Read More
502 Views
6 Replies
Great example, Wyn! We should start posting it as a quiz before providing the solution :-) Maybe I'll start doing it soon myself.

Looks nice, good example based on real life case study

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 formatte

... Read More
46 Views
0 Reply

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 you
... Read More
65 Views
0 Reply