powerquery Questions
6
Solved
I need to convert the Excel matrix FIRST in the table LATER:
FIRST:
P1 P2 P3 P4
F1 X
F2 X X
F3 X X
F4 X X
LATER:
F P VALUE
F1 P1 X
F1 P2
F1 P3
F1 P4
F2 P1 X
F2 P2 X
F2 P3
F2 P4
F3 P1
F3 P2 X...
Busily asked 12/12, 2013 at 11:11
4
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 Vali...
Ajani asked 7/11, 2018 at 20:49
3
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 export:
http...
Escurial asked 7/7, 2023 at 15:55
2
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...
Connett asked 15/7, 2019 at 14:49
3
Solved
In Excel 2016 VBA, I'm refreshing several queries like this:
MyWorkbook.Connections(MyConnectionName).Refresh
After the code is done, and no errors are encountered, I see that the hourglass icon...
Doggerel asked 21/4, 2017 at 17:15
6
I'm specifically trying to convert string in Column A : yyyymmdd to dd/mm/yyyy date format using Power Query Editor in Power BI. I can already perform this in Excel using the formula below:
Any ide...
Carom asked 7/8, 2019 at 10:15
3
Solved
If I have the following source:
#"My Source" = Table.FromRecords({
[Name="Jared Smith", Age=24],
[Name = "Tom Brady", Age=44],
[Name="Hello Tom", Age = null],
[Name = "asdf", Age = "abc"]
}),...
Maggio asked 25/1, 2020 at 23:29
4
So in Excel 2016, they have this neat tool called Power Query, basically a glorified excel table. Every table has steps in it for filtering, removing columns, etc... The first step is the source st...
Arbuthnot asked 18/3, 2016 at 12:32
2
Solved
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-...
Euchre asked 10/4, 2020 at 1:23
3
Solved
I have a REST API, which accepts only POST requests with form data.
I know that in Power Query JSON requests are like this:
let
url = "https://example.com",
body = "{ ""first_param"": ""AAAAA""...
Method asked 3/5, 2018 at 9:47
3
Solved
I concatenated four columns using this code (to prevent null values to be linked together) in Power Query (Power BI Desktop):
= Text.Combine(List.Select(
{ [Col1], [Col2], [Col3], [Col4]
}, each _...
Laise asked 18/9, 2020 at 16:13
4
Solved
When I press refresh button in Power BI (version 2.112.1161.0) I have this error
message
! Table1
A cyclic reference was encountered during evaluation.
! Table2
Loading blocked by failures with oth...
Mediatize asked 11/1, 2023 at 6:29
2
Solved
An Excel table as data source may contain error values (#NA, #DIV/0), which could disturbe later some steps during the transformation process in Power Query.
Depending of the following steps, we ma...
Leisure asked 2/9, 2016 at 19:14
5
I need to find the month difference between two dates (checkIn and Checkout dates) in Power Query (M-query). It can be similar to DAX bellow.
period of months = DATEDIFF([dateCheckIn], [dateCheckOu...
Execrative asked 12/3, 2020 at 18:57
4
Setup:
Windows 7 (at work)
Windows 10 (at home)
Excel 2016 (build 4627 at work)
Excel 2016 (build 8730 at home)
Power Query is setup to import, append, and transform a folder of Excel Files. This ...
Aquileia asked 28/12, 2017 at 1:46
2
Solved
I'm having multiple PowerQuery queries that I would like to feed the value of a cell in my Excel file. In this particular case, the full path to the sourcefile name.
Is there any way I can get thi...
Ipa asked 18/11, 2014 at 7:59
3
Solved
In Power Query, when I right-click on the name of a query (in the left-most pane of Power Query's window) I see selections for "Enable Load" and "Include In Report Refresh."
I cannot find an expl...
Botany asked 6/2, 2017 at 14:53
3
Solved
If I have the following table (shown in the image below), how can I write a grouped query that would concatenate the grouped results?
For this example, I'd want to group by the LetterColumn and ...
Cuspidation asked 18/5, 2017 at 21:53
2
Solved
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).
Nephro asked 8/8, 2018 at 7:16
5
Solved
I have a table that has values for quarters and I need to add a new column that gives me the last not null value as last quarter. For example
ID | Project | Q1 | Q2 | Q3 | Q4 | Current Quarter Val...
Breathe asked 14/7, 2015 at 11:17
3
Solved
Need RANK function in Power Query, Like in Excel, SQL, DAX...
The Best answers for now:
Built in Table.AddRankColumn function available since Jun-2022
Solution with the best performance, at least ...
Bipolar asked 1/7, 2022 at 14:9
2
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.workb...
Marquet asked 9/10, 2017 at 7:7
2
Solved
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...
Aubreir asked 28/6, 2018 at 19:23
3
Solved
How to select row with max value per category in M of Power BI. Suppose we have table:
+----------+-------+------------+
| Category | Value | Date |
+----------+-------+------------+
| apples | 1 ...
Ranking asked 20/7, 2018 at 8:10
6
Solved
What is the difference between
powerpivot,
powerquery
and powerbi?
What are the use cases for each tool?
What are the limitations?
What requirements must be met to use the tool?
Cinder asked 17/4, 2015 at 10:1
1 Next >
© 2022 - 2024 — McMap. All rights reserved.