Stack Overflow
Stack Overflow

Questions tagged [powerquery]

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.

5627 questions
4
votes
3 answers

CountIfs() Equivalent in Power Query M, counts per row within self

I'm attempting to implement countifs() like logic in a Power Query query, but not in a summary table referencing a source table. Rather I want to display all records and have it to be another column (in my actual use case this is necessary). …
ak112358
  • 713
  • 2
  • 13
  • 25
4
votes
3 answers

Power Query check if string contains strings from a list

Is there a way to check a text field to see if it contains any of the strings from a list? Example Strings to Check: The raisin is green The pear is red The apple is yellow List Example to Validate Against red blue green The result would…
Always Learning
  • 41
  • 1
  • 1
  • 3
4
votes
1 answer

This query contains transformations that cannot be used in DirectQuery

Invoking the following function in Power Query editor returns the error below: let Origem = (x, y) => x * y in Origem Invoked Funtion: This query contains transformations that cannot be used in DirectQuery. This is the only query I have…
user882670
4
votes
2 answers

Power query M the IN operator

What would be equivalent of SQL IN operator for Power BI. Just like in clause: where [Column1] IN ('Value1', 'Value2', 'Value3') I am looking for M solution (not DAX).
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
4
votes
2 answers

Index by category in Power BI equivalent to SQL row_number over partition

How to add index by category in M of Power BI with sorting by column. I look for equivalent of SQL: ROW_NUMBER() over(partition by [Category] order by [Date] desc Suppose we have a table: +----------+-------+------------+ | Category | Value | …
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
4
votes
2 answers

How to Find the Most Current Date From a Column in Power Query - MAX()

This is for a Power Query: I am working on a report that compiles information from different dates and I need a column that generates the most recent date in the list and the previous date to the most current one in separate columns: Most Current…
Mike
  • 41
  • 1
  • 1
  • 3
4
votes
2 answers

Power BI create column from nested Record values

I'm trying to create a new column from one of the records in a List type column. The value is the country that corresponds with the Latitude and Longitude fields. The information is retrieved from the Bing Map API, which got using Get Data from Web…
user4192303
4
votes
2 answers

Excel 2016- Load power query directly into power pivot

Is there a way to load power query data directly into power pivot without creating an excel table as an intermediary step? All the examples I've found reference Excel 2010 and 2013. Although the instructions are similar, it does not work in 2016.…
machump
  • 1,207
  • 2
  • 20
  • 41
4
votes
1 answer

Unable to capture errors in VBA when refreshing a query from PowerQuery

I have a workbook with several PowerQuery queries to other workbooks. This workbook is updated annually at each FY - meaning the queries need to point to new workbooks every year. This can of course be done manually, but there are a lot of queries…
Jaspos
  • 365
  • 1
  • 4
  • 17
4
votes
3 answers

Keep only numbers in a string / remove all non-numbers

I have a string column where I only need to the numbers from each string, e.g. A-123 -> 123 456 -> 456 7-X89 -> 789 How can this be done in PowerQuery?
Peter Albert
  • 16,917
  • 5
  • 64
  • 88
4
votes
1 answer

Is there a simple way to count occurences of one text string within another text string?

Is there a simple way, using PowerQuery, to count the occurences of a text string within another text string? For instance, if I have the string, "The quick brown fox jumps over the lazy dog and the lazy dog doesn't notice," how would I easily…
Marc Pincince
  • 4,987
  • 6
  • 19
  • 40
4
votes
3 answers

Get Current Workbook Path - Excel Power Query

Trying to add a custom column and populating the value with the current workbook path name. I have tried Excel.Workbook.name and Excel.CurrentWorkbook() and other objects, but it seems those are limited to pulling data. in VBA this is simply…
dfresh22
  • 961
  • 1
  • 15
  • 23
4
votes
1 answer

What is the difference between Text.Split and Text.SplitAny?

The difference between Text.Split and Text.SplitAny is not clear to me. They both seem to use the same syntax and do the same thing. Would someone please explain the difference(s) between them to me?
Marc Pincince
  • 4,987
  • 6
  • 19
  • 40
4
votes
1 answer

MS Power Query: Create date from month, day, year

How do I create in Power BI Desktop the date from separate columns with data for month, day and year.
dadadance
  • 73
  • 1
  • 2
  • 4
4
votes
1 answer

How to SUM DISTINCT Values in a column based on a unique date in another column of a Power BI table

I have a table in Power BI, where I have two columns like Date and Daily Targets. Daily Targets are always same on the same date so I need a measure to only SUM 1 value for 1 date instead of calculating every row because these two columns contains…
Farrukh Cheema
  • 85
  • 1
  • 2
  • 7
1 2 3
…
99 100