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
5
votes
2 answers
Can I get data from power query to a vba variable?
I have a power query function, e.g. getData with one parameter filename
Can I invoke that function directly in a vba variable? Something like:
Sub MyTest()
Dim MyVar
MyVar = ThisWorkbook.Queries("getData").Invoke("mytable.xls")
MsgBox "The…
Роман Коптев
- 1,555
- 1
- 13
- 35
5
votes
1 answer
How to set OR logic between two slicers for the attributes from same dataset?
I'm trying to set OR logic between the two slicer filters. The two slicers are from same dataset. Below are more details:
My source table:
Visual with slicers:
My goal is, if I select any value from slicer 1 and also from slicer 2, my visual…
user3588007
- 227
- 3
- 15
5
votes
1 answer
PowerBI: Last 3 Non Blank Values from column
I have a table where I get the status of the employees tasks day by day.
As in below image I get the values(Confidential, so just entered dummy data).
Then I did unpivoted the dates column and made a copy of that and split with space so that I can…
Bunny
- 409
- 3
- 21
5
votes
1 answer
Converting Python REST API Calls to Power Query
I am working with a cloud-based service that is exposed using OAuth REST API.
I have a working Python script, with which I am able to first post username, password and api token to get an authentication token. Then I use the api and authentication…
arun
- 10,685
- 6
- 59
- 81
5
votes
1 answer
Query not supported in DirectQuery (PowerBI / PowerQuery)
A follow up from Display rows where foreign key is present in PowerBI
I now have the following query that retrieves data via a join and display as a table:
let
Source = Sql.Database("server\database", "Mclaren"),
dbo_Capability =…
taylorswiftfan
- 1,371
- 21
- 35
5
votes
1 answer
power query (M) create record from a text type column
when retrieving data from a web based API into the Power BI, I did end up with a quoted record (text type) which I am unable to convert to a record. There must be a simple way to do this, unfortunately I was not able to find it.
So simply record =…
Jan
- 437
- 4
- 15
5
votes
1 answer
Power BI (Power Query) Web request results in "CR must be followed by LF" Error
When you use the Web.Page(Web.Contents('url')) function to read a table from a web page, some sites will cause an error due to inconsistent linefeeds.
DataSource.Error: The server committed a protocol violation. Section=ResponseHeader Detail=CR…
James White
- 2,062
- 2
- 24
- 36
5
votes
6 answers
How to Do Countifs/Sumifs in PowerQuery M.Language Formulas?
Is it possible to Do Excel's Countifs/Sumifs formulas in powerquery Formulas? Let's Say I have [Column1] as "Criteria range" and [Column2] as "Criteria" (Example criteria [Column2] text contains "Product") and I want Count/Sum [Column1] Cells in the…
Behnam2016
- 251
- 2
- 9
- 14
5
votes
3 answers
Power Query: transform a column by multiplying by another column
I want to do something similar to Power Query Transform a Column based on Another Column, but I'm getting stuck on how to modify the syntax for my particular goal.
Similar to the linked question, assume that I have the following table:
Table…
Traci
- 91
- 1
- 2
- 6
5
votes
1 answer
Is it possible to write a custom Power Query Connector?
In Power Query under “From Other Sources”, we see a number of specialized providers (Facebook, SAP, SalesForce, etc).
I am interested in writing a custom provider to access a local proprietary data store that cannot be consumed via any of the…
tbone
- 5,715
- 20
- 87
- 134
5
votes
7 answers
How to achieve running total with power query?
I want to do a running total with power query like I did with Tableau software before. Does anyone have ideas, thanks in advance!
Summer_Solstice
- 103
- 1
- 2
- 8
5
votes
2 answers
WAAD Authentication with WebAPI OData service consumed by Excel PowerQuery
I've created a WebAPI OData 3.0 web service with an OWIN middleware, which is configured for authentication with Windows Azure Active Directory.
The ODataControllers are marked with an [Authorize] attribute, and the IAppBuilder is configured as…
Or B
- 1,857
- 15
- 22
5
votes
2 answers
Power Query error in connecting to data source using an encrypted connection
I am trying to connect to a db running on (localdb)\v11.0 using power query, but I always got the following error:
DataSource.Error: Microsoft SQL: The instance of SQL Server you attempted to connect to does not support encryption.
Details:…
carlos
- 815
- 2
- 14
- 27
4
votes
2 answers
How to import a Jira board into excel using exce | powerquery
I would like to have a powerquery in my excel file which pulls all the issues from a jira board.
I have:
created a filter in jira for the issues already.
captured the link to the csv…
Freelancer
- 153
- 1
- 15
4
votes
1 answer
Generate cumulative returns over a dynamic period in Power BI
I have a table that contains the open, close, high, low, and adjusted close prices for a stock from 2015 to 2021. My goal is to produce cumulative returns over any date hierarchy member (year/quarter/month/week/daily).
Below is a snapshot of what I…
jamesbond
- 489
- 1
- 4
- 8