Microsoft Power Query for Excel enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Power Query also provides you with the ability to search for public data from sources such as Wikipedia.
Questions tagged [powerquery]
5627 questions
8
votes
2 answers
authenticating with Excel Power Query against .Net Odata Web Api
I am trying to use Power Query to download an Odata Feed that I created using .net Web Api 2 and the OData v4 nuget package.
I'm trying to access an Odata feed that requires authentication. When I edit the authentication type in power query, I'm…
TWilly
- 4,863
- 3
- 43
- 73
8
votes
3 answers
Power Query: sort by custom list
I have a list of time seasons within school years:
"Fall 12-13",
"Winter 12-13",
"Spring 12-13",
"Fall 13-14",
etc.
I want to sort a large number of rows chronologically based on these values. In Excel it is possible to sort by a custom list where…
zacksg1
- 91
- 1
- 2
- 5
7
votes
2 answers
How to import JSON file to Excel without coding?
Let's say you have a file named input.json which contains an array of objects defined in standard JSON format. Something like:
[
{"name": "notebook", "price": 500.00, "rate": 4.2},
{"name": "sd-card", "price": 60.49, "rate": 3.5}
]
How can…
Mostafa Aghajani
- 1,844
- 2
- 14
- 19
7
votes
1 answer
SUM IF in Power BI
Is it possible to SUM on conditions in Power BI?
I have a column that contains the values UK and Italy alongside other columns: employee and hours spent.
I want to make a measure to show total hours spent by an employee in Italy and another to show…
Shoaib Maroof
- 369
- 1
- 3
- 13
7
votes
1 answer
How to refer columns in Power Query by index or position?
I have a line as below in Power Query. So instead of referring it by name, I want it by position dynamically. Can someone help here, please
#"Filtered Part Desc" = Table.SelectRows(#"Removed Columns3", each
…
Sam K
- 332
- 1
- 6
- 19
7
votes
3 answers
Always Allow Native DB Queries
In Excel 2013 how can I change options (or is it possible) to always allow native DB Queries?
Can someone walk me through how to set this up?
Habib Inman
- 151
- 1
- 1
- 7
7
votes
5 answers
How to search multiple strings in a string?
I want to check in a powerquery new column if a string like "This is a test string" contains any of the strings list items {"dog","string","bark"}.
I already tried Text.PositionOfAny("This is a test string",{"dog","string","bark"}), but the…
Behnam2016
- 251
- 2
- 9
- 14
7
votes
2 answers
Is there a short name for Power Query Formula Language (informally known as 'M') that isn't 'M'?
As always, naming things is hard.
Power Query Formula Language is long
M is short
Is there a nice middle ground or are we sticking with 'M'?
For example, there might be conflicts when it comes to creating things like Syntax Highlighting packages in…
Jay Killeen
- 2,832
- 6
- 39
- 66
7
votes
2 answers
Power Query -> Transform Comma to Period and Obtain Decimal Numbers
I'm working within Excel Query Editor (Power Query) and I have a table with many columns.
Some of the numbers in those columns have a period (".") and they are properly recognized as decimal numbers, but some of them have a comma (",") and they are…
jb007
- 243
- 1
- 5
- 17
6
votes
1 answer
How to store credentials in Power BI DataConnector?
I'm building a custom Power BI DataConnector which uses OAuth. I'm following the github example. But this stores client credentials (required for the 'code flow' in OAuth) as plain text files. Is there a secure alternative to this ?
Neeraj
- 2,376
- 2
- 24
- 41
6
votes
2 answers
How do I convert #datetime to my local timezone?
A column in Power Query converts a JavaScript timezone into UK format:
= #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Timestamp]/1000)
But this produces the wrong output (-1hr difference), presumably due to UTC / GMT+Summertime…
Adam
- 1,932
- 2
- 32
- 57
6
votes
1 answer
How to export an R dataframe to a Power Query table
I'm using an R script within Power Query to do some data transformations and return a scaled table.
My R code is like this:
# 'dataset'
user882670
6
votes
3 answers
Select row with MAX value per category Power BI
How to select row with max value per category in M of Power BI. Suppose we have table:
+----------+-------+------------+
| Category | Value | Date |
+----------+-------+------------+
| apples | 1 | 2018-07-01 |
| apples | 2 |…
Przemyslaw Remin
- 6,276
- 25
- 113
- 191
6
votes
2 answers
Excel - Power query data refresh via Python
I was trying to refresh a power query - external data in Excel via Python using following code
import win32com.client
import time
xl = win32com.client.DispatchEx("Excel.Application")
wb = xl.workbooks.open(fileName)
xl.Visible =…
emudria
- 133
- 1
- 12
6
votes
2 answers
Power Query: Parsing HH:MM:SS durations that are greater than 24 hours
I'm trying to Query data from another data source in Excel, and am receiving parsing errors for any durations larger than 24:00:00.
Expression.Error: We couldn't parse the Duration literal. Details:
51:33:08
How do I correctly have excel…
Douglas Gaskell
- 9,017
- 9
- 71
- 128