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
1
vote
1 answer
Average of percent of column totals in DAX
I have a fact table named meetings containing the following:
- staff
- minutes
- type
I then created a summarized table with the following:
TableA =
SUMMARIZECOLUMNS (
'meetings'[staff]
, 'meetings'[type]
,…
verstehenmonk
- 23
- 5
1
vote
1 answer
Duplicate Rows in Power BI multiple times by a number indicated in another column
The idea of the problem I am having is that in Power BI I have a table like:
col1 col2
entry1 1
entry2 2
entry3 1
I would like to create a table of the form:
col1
entry1
entry2
entry2
entry3
That is you duplicate each row by the number specified…
James
- 15
- 1
- 6
1
vote
1 answer
How to fix the Power query code error for splitting a text column based on a criteria
Split the text values in a column based on the data type of the first character in each record.
I need to have the new (custom) column return the text before the first " " delimiter if the first character of the text is a number, otherwise return…
Julius Blue
- 53
- 1
- 6
1
vote
1 answer
How to search entire table for a phrase in Powerquery
How to check in powerquery, if an entire table contains the search value in any column or any row. Say we have a table:
+------------+-----------+---------+
| column1 | column2 | column3 |
+------------+-----------+---------+
| apple |…
Przemyslaw Remin
- 6,276
- 25
- 113
- 191
1
vote
0 answers
Reading value from a cell & code alignment
I have been trying to fetch api data from a web service in the past couple of days. I am unable to do it in a more efficient way and I am not getting help on my request too. After trying for long, I somehow have managed to get it from Excel Power…
Sabha
- 621
- 10
- 32
1
vote
1 answer
How to add a derived column to a pivoted table using Excel PowerQuery by referencing column position
I am creating a regular report using PowerQuery in Excel that has pivoted data. The pivot has the latest four quarters as column names in date order (e.g. 30/09/2018, 31/12/2018, 31/03/2019, 30/06/2019). The values in each date column are numeric.…
racho
- 87
- 10
1
vote
1 answer
If then Statement using Two Different Date Ranges
In Power BI, I'm using a period table that applies steps to take today's date, sets up the ranges, transforms the ranges into a table, and then sets the output in Power Query. Everything works as it is supposed to but the focus is at the ranges…
C.Mayers
- 91
- 1
- 9
1
vote
1 answer
Converting Single Line Employee Records to Pivoted Sankey Layout
Looking to do the following in PowerQuery preferably in PowerBI (but am comfortable with taking advantage of Python):
I have records in this layout:
Employee ID, Employee Classification, Start Date, End Date
9999999, Financial-1, March 17 2008, June…
Cyrus
- 11
- 1
1
vote
1 answer
Power Query Function - Optional Arguments - Token Literal Expected
I am trying to create a function in powerquery that will accept optional arguments. The purpose of the function is to establish a common directory that will be used for text imports in other queries within the workbook. Typically for our models,…
need_java
- 127
- 2
- 4
- 13
1
vote
1 answer
How to extract Queries metadata from Power Query Editor
I am looking for a way to extract power queries metadata from power query editor to spreadsheet or word for documentation purposes to understand the transformations or formulas applied in each query present in power query editor.
I have read…
Thirupathi Rao G
- 11
- 3
1
vote
1 answer
Excel Power Query Parameterized IN Clause for SQL
I have an Excel Workbook that gets data from an ODBC data source using an SQL statement that has an IN Clause in the Where Statement. In the Power Query Editor, I typed in my ODBC DSN and the SQL below. I would like to replace the IN clause with…
Ray Pietrzak
- 71
- 1
- 5
1
vote
1 answer
OLE DB or ODBC Error: We cannot convert the value null to type Logical
I am calculating following calculated column in query editor:
End Date =
if [Date_1] <> null
then [Date_1]
else if [Date_2]<>null
then [Date_2]
else DateTime.Date(DateTime.LocalNow())
Based on this column, the following table is…
Pratik Bhavsar
- 808
- 8
- 32
1
vote
1 answer
How to use SQL temporary tables in Power BI
I am building a Power BI report that uses a set of SQL temporary tables as source. I would like to provide the user the opportunity of selecting a month from a date slicer and based on that selection, the report would show the valid data AS OF that…
ANieder
- 223
- 5
- 15
1
vote
1 answer
Preserve Formatting in Power Query
I cannot seem to preserve previously set formatting upon adding a new column in a Power Query.
Source table has values as percent with two decimals
I import into PQ and it applies what it thinks is proper formatting:
I change the formatting…
Ron Rosenfeld
- 53,870
- 7
- 28
- 60
1
vote
1 answer
Transfer Access Code into Oracle Syntax (Power Query)
I am just woorking on to transfer a Access Query into Power Query (Excel).
I think there are some challenges regarding the terms/ wording.
Its a Oracle DB.
The original code looks like this one:
SELECT
POOLK.DATA.DOC,
…
user10732287