Home

Get and Transform Data

96 Conversations

Latest Activity

Custom List Message Item

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
41 Views
4 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

How do I get formulas on a sheet to display?  I am a teacher - I need to "see" the formulas that students have used in order to grade the assignment.  In Office 2013, we would use the Fn + ~ key and the formulas for the entire sheet would show up.  We can

... Read More
51 Views
4 Replies
You could use the FORMULATEXT function in adjacent cells.

I have tried to connect to 20+ websites that require sign-in credentials using Power Query without success.  I am able to login using Chrome so the credentials are correct.  The error I receive is below.  I have clicked Edit Credentials and re-entered the

... Read More
223 Views
5 Replies

Hi there. Can you clarify what authentication method you're using? When signing in via Chrome, are you using Basic Auth, entering a username/password via a sign-in page,

... Read More

Hi,

We've been suffering from this problem for months and trying to get MS attention - maybe in the wrong places :-(.

see: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/20166799-provide-more-diagnostics-for-the-data-from-web-p

... Read More

I am in Query Editor, and there is no option for "Column from Example" in my Query editor when i choose Add Column.  I restarted my computer, double checked the add in's into my excel. Everything is in there that is fine. I have Office 365 Plus. 

 

The only

... Read More
27 Views
0 Reply

We have a large data model that worked as expected in Version 1701 (Build 7766.2099).

 

Multiple users in our organization have updated to version 1705 build 8201.2193, and in all cases - individual query and connections fail and refresh all fails as well.

 

... Read More
119 Views
2 Replies

Same here.

Quite a big problem for me.

 

HELP

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
97 Views
5 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

Hi! When adding a custom column I prefer to add the logic as "basic" code. When editing the logic sometimes when the logic is "simple" I get a form (see attached screenshot). Is it possible to disable the forms function and always getting the "pure code"

... Read More
34 Views
0 Reply

I am linking data from another workbook into my current worksheet.  Can I show the file name I'm linking to in my current workbook?  That is, can I link the source filename into a cell in my current worksheet?

Allan

235 Views
24 Replies
I would enter the source filename into a named cell (e.g. SOURCENAME) in the source file and then simply reference that directly ='Demo 1.xlsx'!SOURCENAME in the current ... Read More

Hello,

 

I am the lead scout mentor for a local high school robotics team. This year we are trying to automate our scouting data collection as well as our data analysis procedure. I apologize for the length, but it is easier to answer a question if you have

... Read More
70 Views
0 Reply

When i doubleclick a .doc or .docx-file in windows explorer word starts an the appropriate file is loaded for working.

Ther ame procedurefpr .xls or .xlsx dosn´t work for excel. Only, excel starts baut the fiel is not loaded.

What´s wrong?

In Apps and Featur

... Read More
51 Views
1 Reply

I am trying to use Excel to pull a list of Documents from a Document Library in SharePoint Online. I can see a Title Column if I disable Content Management, but I cannot see the Name column even if I try to add the column after loading the initial Query.

... Read More
96 Views
5 Replies

I have just tried this with one of our document libraries that have metadata.  There was a field that when i expanded allowed me to see the MetaData fields and the 'name'

... Read More

Hi,

So I'm recently doing a project where I have to get data for each user from a website url, but the website url varies on the username. For example the begining of the URL is www.site.com/username=. I have all the usernames in cells A1:A201, so for the

... Read More
70 Views
1 Reply

Hi Salvatore

 

You'd need to use a custom function

 

I've attached a demo file here for you to look at

 

 

I have an XML-file that i had successfully imported til excel. I didn't have any .xsd schemefile so i let excel "create" one automatically from the xmlfile.

The data created just fine in two rows and the mapping is shown in teh window.

 

BUT when I intend to

... Read More
51 Views
1 Reply
This is because Excel cannot export XML files which are more than one level "deep". But is can import such files without a problem.

I am trying to import data into Excel from a list on SharePoint Online. I am able to connect to the list using the standard data connections feature in Excel, which allows connection to various web services. However, when making a connection, all the rows

... Read More
77 Views
1 Reply

Did you try to Edit the Query before you loaded the data?

I am trying to manage individual players scores we have weekly scores, players do not always play what I want to do is reverse the order of the scores and have last first and compress out any cells without scores so I will have last score, next to last, e

... Read More
86 Views
4 Replies

Hi Carlos,

 

To remove blank rows in the range please check this http://www.techrepublic.com/blog/microsoft-office/a-quick-way-to-delete-blank-rows-in-excel/, after that so

... Read More

In Excel 2016, Version 1705 (Build 8201.2193), if I have a query from Get & Transform that is returned to the worksheet, it reorders the columns to match what is in the query editor every time I refresh - even if I check the box to "Preserve column sort/f

... Read More
104 Views
1 Reply
Hi Chris,

I hadn't noticed that change, but I can see what you mean. I guess it would be best practice to sort your columns in Power Query editor anyway but an interesting... Read More

Trying to think of a better way other than copy/paste to do this...

 

I have 5000 lines of data in one column

a

a

a

a

b

b

b

 

c

c

c

c

c etc...

I want to generate a second column with a number, that changes when the data in column A changes to that the final product looks

... Read More
73 Views
2 Replies

In Cell B2:

=IF(A1<>A2,B1+1,B1)

Drag down.

Best Response

Using ODBC linking, the dates comming over into Excel are in text format "YYYYMMDD".  I have tried every way I can to convert to a numbered date format, but nothing works, even other suggestions in this forum.

 

Does anyone have a recommended solution to th

... Read More
58 Views
2 Replies
THANK YOU!! Worked perfectly!!

Hi Kevin,

If that's a text you may use Data->Text to Columns selecting on third step of the wizard proper date format (YMD)

 

YMD.JPG

 

Read More

Oh my! After reading some of the questions on this blog, i have to admit that i am a newbie to pivot tables and my question is rudimentary by comparison. I am having difficulty getting my data to appear the way i want it.  I have been trying to copy/paste

... Read More
53 Views
1 Reply

Hi Sari,

 

1) Add as rows

2) Adding the field right click and uncheck Subtotals

Subtotal.JPG

3) When right click on previous field and in Field settings change it on to show in tabular fo

... Read More
Best Response

I am trying to perform a web query on a password protected site that requires criteria to be filled in to generate a report that is an additional pop up window.  I need the data from the pop up window report.  Anyone have any idea how to do this with the

... Read More
73 Views
1 Reply
I just got a hold of the company hosting the site and asked for read only rights to the sql database servers and they granted it. problem solved

Hello,

I have this idea in my head but as a novice in excel don't have clue how to make it happen.

I have two workbooks. One is the source of data and other one is the inventory workbook that i need to update with data from source data. I would like to know

... Read More
46 Views
1 Reply

Hi Vaclav,

 

You may use Power Query (starting from Excel 2010), aka Get & Transform in Excel 2016

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

... Read More
182 Views
1 Reply

Hi,  don't load the connection, edit the connection

 

edit.jpg

then in the Power Query screen, filter the 'Name' column to get the files you want, then click on the column 'Content'

... Read More
Best Response

I have 2 files with unique ID & name; need name from 1st file into 2nd but they're not in same order

46 Views
1 Reply
Assuming you have two files; Book1 and Book2 with ID & Name columns as Column A and Column B Enter the following formula to the cell of Book2 that you want to call the va... Read More

i have a table which, because of its size (number of rows), i had to divide into two tables in separate worksheets. but the tables have the same column names. i want to make a pivot table with these tables as input. how can i make the pivot table recogniz

... Read More
90 Views
4 Replies

Hi Juan,

 

you can have one table only as a source of data for Pivot Table. Does you source data have to be in an Excel table, or it can be in some other format (e.g. CSV)?

... Read More

I am using an Excel Table that the user can modify as input for a Power Query. The result is also part of the workbook. The user can not only alter data (allowed) but also add a total row (menu table: add total row), with all variants. If these totals are

... Read More
68 Views
1 Reply

Hi Bart,

 

Totals is the property of the table, you can't check it directly in the query. As workaround could be:

In any cell check the totals for your table like

=Table1[[#T
... Read More