Home Excel

Get and Transform Data

17 Conversations

Latest Activity

Custom List Message Item

This function always had two parameters as it is documented for today

Excel.Workbook(workbook as binary, optional useHeaders as nullable logical) as table

https://msdn.microsoft.com/en-us/library/mt260859.aspx

 

However, with the latest PQ update connector to

... Read More
38 Views
2 Replies

Hi Sergei!

 

Originally, Excel.Workbook used to read the entire workbook data to accuratly assign types to the columns. This was EXTREMELY slow on large workbooks.

 

Havi

... 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
31 Views
1 Reply

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

 

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
86 Views
3 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

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
102 Views
3 Replies

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

Power Query / Get & Transform is amazing at connecting to and transforming data.

 

It's capable of all sorts of data cleansing and manipulation.

 

In this video I step through one way that you can manipulate daily data and dates to then present your reports a

... Read More
83 Views
0 Reply

Get & Transform (Power Query) is amazing at connecting to and transforming data.

 

You just build the connections, do the transformations once, and then simply refresh anytime you want to update the data.

 

Power Query is built into Excel 2016 and is a free d

... Read More
100 Views
2 Replies

Wyn, good job!

Excel 2016 includes a powerful new set of features based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.

 

Today, we are pleased

... Read More
119 Views
1 Reply

Olaf, since this is Tech Pro community, perhaps it's worth to share insiders news as well here, for example Dec 2016 Excel update for insiders - https://support.office.com/en-US/article/What-s-new-in-Excel-2016-for-Windows-5fdb9208-ff33-45b6-9e08-1f5cdb3a6c73

... Read More

I was able to obtain data from Managed Metada column in SPO list using SharePoint.Tables("URL", [ApiVersion=15]) and FieldValuesAsText technique as @John White suggested. And failed to do the same in SP2016, Google didn't help.

Does someone know if that wo

... Read More
150 Views
2 Replies

@Bill Baer do you know?

are they only different in terminology and basically since o2016 its changed name as "Get & Transform"?

154 Views
5 Replies

I agree with the other replies and wanted to add that it seems like a small difference until the user is in a large enterprise enviroment and then add-in's might not be a

... Read More
I guess so. Power Query is add-in you may download and install for Excel2013, staring from 2016 it's built-in and renamed as G&T

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
209 Views
5 Replies

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

My preference is to always use Power Query for loading data into Power Pivot, however last week I hit an issue where it was taking 3+ minutes to refresh a simple connection to a SSAS database.

 

I had applied 2 Filters and was returning 50,000 records from

... Read More
221 Views
3 Replies

It's possible that the query we're generating is inefficient. If you are willing to send me the the M query you are using in a private message, I can take a look and see

... Read More

Does anyone know of an easy way to copy a worksheet from a workbook containing Power Query work to a New Workbook without any of the underlying Power Query objects?  I just want the Excel table in a new workbook!

88 Views
3 Replies
My guess is you shall remove all data connections in first workbook (or its copy) and after that copy all sheets from first workbook to another one. Power Query won't be ... Read More

In my work environment SharePoint 2010 is still very much alive and I run several Power Query scenarios with SharePoint 2010 data.  Yesterday, I worked on a query that includes a multi-value choice column. In the Query Editor, the column shows as "Table"

... Read More
200 Views
2 Replies
Have a look at the FieldValuesAsText column. I blather on about this approach here: http://whitepages.unlimitedviz.com/2016/07/simplified-method-working-sharepoint-data-power-bi/ Read More

Header.PNG

 

I posted this in the BI and Analysis section before realising this dedicated Get & Transform section was available...

 

Power Query is fantastic at all sorts of wonderful data transformations and can save you huge amounts of time.

One common task is the nee

... Read More
96 Views
0 Reply