Home

Get and Transform Data

67 Conversations

Latest Activity

Custom List Message Item

How can I change data sources for Pivot Tables tied to the local dataset that was built with PowerPivot & PowerQuery to the new replacement data model that we built in PowerBI Desktop and published to the Power BI Service?

 

I have a very large Excel workbo

... Read More
34 Views
3 Replies

Hi Eric,

 

I don't know how to change all connections automatically, just intresting why did you use Power BI Desktop to publish the data model on Power BI Serrvices? I'd u

... Read More

After updating to Version 1707 (8326.2052) today Excel crashes when trying to connect to OLAP cubes (SSAS - Analysis Services). 

 

We have tried creating new connections with no luck. Excel crashes every time. We have tested on several lenovo models, 450s,

... Read More
481 Views
26 Replies

Thanks all, is working fine after several tests, deploying the update!

 

 

Thank you for reporting it, will sent it to the right people and keep you updated. 

 
Read More

Hello all,

 

We have also had to downgrade for now. We have been looking at this with Microsoft premier support and after numerous attempts at a fix we have only been left

... Read More

Same issue here since latest update.
Downgraded to previous office version as mentioned previously and it worked.

"C:\Program Files\Common Files\microsoft shared\ClickToRun

... Read More

Any feedback on this. Analysis Cubes still not working, yet somehow inconsistent. we have many users that did update to the 1707 version that do not have issues. there is

... Read More

I have a workbook that uses the ODBC interface part of Microsoft Query to query a DB2 database.  The workbook actually queries over 20 different tables.  I have one cell in the workbook that is the main search parameter for all the queries (for example WH

... Read More
119 Views
9 Replies

Hi John,

 

I didn't work with DB2, just my guess if that could work

 

Source=DB2.Database("server", "database", Query=["SELECT something" & pFromMyCell])

where pFromMyCell is

... Read More

So, what happened to the paramaeters with web queries functionality?  

When executing a web query, it USED to be that we could specify the query in brackets within the URL.  Now, however, an XML query from a web page results in a query editor where the que

... Read More
47 Views
2 Replies

Hi Max,

 

You still may use "old style" connectors. In File->Options->Data enable under section Show legacy data import wizards.

 

To initiate connection use Data -> Get data

... Read More

After having a view looks on variouse forums I found that I was not the first with the same data filtering requirement of my source data.

 

Case:

You have a Dataset where you need to append frequently new data from Folder in which people drop new Excel files

... Read More
67 Views
3 Replies

Hi,

 

I don't know if other options exist, perhaps they are, as for me i'd take #2 and almost sure it'll be most efficient.

 

G&T is slow and inefficient to work with big arr

... Read More

I'm using G&T to connect to a DB2 database.  I have 4 queries in the workbook that connect to the same database.  If I run all 4 queries and my password has expired, my ID gets locked because of too many tries with old password (our host system locks on 3

... Read More
22 Views
1 Reply

Hi John,

 

If you connect to the same database you may use only one source query and the rest as a references on it. The idea is here https://powerpivotpro.com/2017/05/power-query-magic-part-1-always-good-references/

Read More

Hello,

 

I would like to Get & Transform from a report, but the process for me to get to my data requires that I first go to the website and log-in, then click a link to request a new report.  Every time I need new data, I clikc the link and a new download

... Read More
21 Views
0 Reply

Hi,

I have a text file with millions of numbers separated by comma and semicolon.

How do I get the text file transform to excel file where it will identify number before/after comma to put in column and number before/after semicolon to put in row, automatic

... Read More
93 Views
8 Replies

Hi,

 

you can use Power Query for this purpose. If you put the data file name in the range 'setFileName' and the file path in the range 'setFilePath', then you can use the

... Read More

Does that mean each number in your text file has either comma or semi-colon embedded but not both? Can you share some sample data? May be 4 or 5 numbers...

Hi. I have a file with over a million rows in SHEET 1, plus 50 columns. Many of the rows are duplicate IDs (column A), but for different years (column B). I want to select only the most recent entry for each ID. I want to populate SHEET 2 with the same 50

... Read More
53 Views
1 Reply

Hello,

 

maybe, this could be done in PowerQuery. I have uploaded a sample file including a Get & Transform Query. For creating the query, I transformed the data range to a

... Read More

I was using 'Power Query' addin in Excel 2010 to get data from an old .xlsx file. It was working perfectly fine and loading data into table in current workbook until I 'Unpivoted' some columns. I don't understand why 'Unpivot Other Columns' operations can

... Read More
103 Views
3 Replies

Hi there,

 

What happens if you remove the "unpivot" step? Does it work?

 

In any case, would you mind sharing the workbook, please? Just make sure it doens't contain any

... Read More

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?

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

I have a very complex financial model made up whose output changes based on the 2 inputs. I want to make a table where the column has all possible values of input 1 and row has all possible value of input 2. Each cell in the table should have the output c

... Read More
64 Views
1 Reply

Hi

 

You may want to have a look at data tables.

 

Read More

Hi!

I have a problem that I cant solve. Somthing is wrong, but I cant figuer it out! I would be very grateful if someone have the time and the knowledge to help me! Thank you for your help!/ Marit Poutamo

 

The formula goes like this; =INDEX(´Values´B4:B43(´

... Read More
50 Views
1 Reply
Hi Marit

Can you give a bit more explanation if what you are trying to do and ideally post some screenshots or a sample file.

It's not clear from your description what you ... Read More

 

Hi, does anyone know if there's an efficient way to transform columns of data as seen in left image below to a new set that looks like image on the right? Excel.png

Read More
212 Views
8 Replies

Hi Sally,

 

please find attached my attempt to solve it using Power Query. The query automatically extracts the names of flavours and creates up to 3 flavour columns and 3

... Read More

This looks like it could be solved with an IF statement:

 

=IF($A2="","",IF($A2=C$1,1,"")) for flavours.

=IF($A2="","",IF($B2=F$1,1,"")) for years.

Read More

Does anyone know how to configure the new query for MySQL correctly?

 

I can connect to the MySQL database server using MySQL workbench TCP/IP over SSH (port 22) and then Localhost (127.0.0.1) (no SSL) port 3306.  But Excel won't connect to this server. Wha

... Read More
47 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
32 Views
0 Reply

We, as a company are maintaining a OLAP cube in MS Analytical Services. We use MS SQL Server Management Services. I am trying to connect Excel 365 online to the OLAP cube. I should be able to refresh connection from Excel 365.

 

Things I have tried:

1. Conne

... Read More
130 Views
8 Replies
Hi Ajinkya,

Were you using Get & Transform to connect to the OLAP cube? What method were you using.

Are you then trying to refresh that file using Excel online? Read More

Hi all,

My Office 365 has recently updated with the new wizard for importing data. I work with a number of text files which I import into excel using the Text Import Wizard. I would insert Fixed width columns and manually create column breaks as the data d

... Read More
201 Views
7 Replies

Hi Ross,

 

In File->Options->Data enable "From Text (legacy)" wizard and after that you may use it from Ribbon menu Data section

Get Data->Legacy wizards.

 

And yes, you may u

... Read More

I am creating an Excel Online Survey in OneDrive for business with this article's procedure.

 

The survey works fine until a new column is added, in Excel Online's edit view, with an unique identifier for each row. At this point it seams as if the survey ce

... Read More
90 Views
1 Reply

I no longer need to solve this:

 

With the recent availability of Microsoft Forms Preview to my Enterprise licence I have been able to:

 

1) Share a survey in Forms to get us

... Read More
Best Response

Hello,

 

I am strugling trying to transpose data while conserving links into a different worksheet. I have been searching on Youtube and forums but I havent found it and I wonder if there is some not to complex possible work around? I am using Excel 2016 in

... Read More
58 Views
1 Reply

Hello,

 

how about you share a file or at least a screenshot of the current data, then manually mock up the desired result and explain in context. 

 

Excel has the Transpose(

... Read More

Check out my suggestion on UserVoice.

 

Suggestion:

Using the legacy wizards, it is possible to refresh a "Microsoft Access Database" connection type in under a second, regardless of the number of tables being imported.

 

However, the new query imports from Ac

... Read More
116 Views
3 Replies

Hi Will,

 

Can you please elaborate on your tests? How large is the dataset? Did you choose to create relationships? Are you importing into the data model or grid?

 

Guy

... Read More

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
473 Views
7 Replies

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

Hello Folks :)


As recomended by the Office Support page (link), I'd like to start a new conversation on how to ingegrate PostgreSQL in this forum as I cant find (after 2 hours of searching) any simple sollutions out there.


Am tyring to get Excel (2016 Profe

... Read More
112 Views
0 Reply

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
409 Views
4 Replies

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