FAQ Database Discussion Community


Power query function optional arguments

excel,powerquery
How can I create a power query function with an optional argument pls? I have tried various permutations of creating the function syntax, presently like this: let fnDateToFileNameString=(inFileName as text, inDate as date, optional inDateFormat as nullable text) => let nullCheckedDateFormat = Text.Replace(inDateFormat, null, ""), value = if nullCheckedDateFormat =...

How can I perform the equivalent of AddHours to a DateTime in Power Query?

excel,datetime,excel-formula,powerquery
I've seen PowerQuery expressions that can subtract days from a datetime, like DateTimeZone.UtcNow.AddDays(-1). However, I'd like to subtract hours from a DateTime, not days. As I don't see a function like AddHours() in the Power Query specification, is there a robust way to go back a number of hours? For...

Power Query - remove characters from number values

numbers,character,formula,powerquery
I have a table field where the data contains our memberID numbers followed by character or character + number strings For example: My Data 1234567Z1 2345T10 222222T10Z1 111 111A Should Become 123456 12345 222222 111 111 I want to get just the member number (as shown in Should Become above)....

How do I create a custom index column in PowerQuery?

powerquery
I have the following data in PowerQuery: | ParentX | A | | ParentY | A | | ParentZ | A | | ParentY | B | | ParentZ | B | | ParentX | C | | ParentY | C | | ParentZ | C | I want to...

Join a path variable to each row in the import csv file

excel,powerquery
I have many Import files, which look like this So there are sales values per Team Member, but NO period inside. The period is coded in the Path like: AllData\201501\Revenues.txt AllData\201502\Revenues.txt AllData\201503\Revenues.txt I want to have the Periode from the path on each data row, so my final output table...

How to convert JSON-string to Binary using a Record? Web.Contents + POST + Power Query

powerquery
Web.Contents method takes Content as Binary I use this code. It works query = "{ ""field1"" : ""value1"", ""field2"" : ""value2"", ""field3"" : { ""sub_field_3_1"" : [""value_3_1_1"", ""value_3_1_2"", ""value_3_1_1""], ""sub_field_3_2"" : [""value_3_2_1"", ""value_3_2_2"", ""value_3_2_1""] } }", content = Text.ToBinary(query), Web.Contents("https://my_url", [ Headers = [#"Content-Type"="text/xml; charset=utf-8"], Content=content ]) I understand, it...

Excel Data Imported as '15/2, Macro to convert to number?

excel,vba,excel-vba,powerquery
I'm importing data from an API into excel via power query. As some of the data is in fractions this is causing a bit of a nightmare. I can't use 'number' because of this and it throws an error, so I have to import as text. When doing so the...

PowerQuery multiple files and add column

excel-2010,powerquery
I have the inline PowerQuery to automate my weekly reporting. Since I am new to power query I followed up this tutorial and try to add a custom column so I can use it to see week over week improvements, the thing is that the column that is added is...

How can I save a path criteria when I import from folders?

excel,powerquery
I import many textfiles from a folder with subfolders, everything works fine so far. My folder has the same text file in many folders for different Periods, so e.g.: AllData\201501\Revenues.txt AllData\201502\Revenues.txt AllData\201502\Revenues.txt ..... I need a Part from the path like "201501" inside my query which is preparing the Revenues....

Calculate the number of business days between two dates in power pivot

powerpivot,dax,powerbi,powerquery
I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot. I do the same in T-SQL using the following query DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) - CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END...

Power Query: How to import numbers as text

text,import,numbers,powerquery
I have a csv file to import to Power Query (250,000 records). One field contains our fee codes which are usually entered as numbers but sometimes contain characters (e.g. 17, 17A, 67, 67A, etc). When I import the file to Power Query, the field is treated as a numeric column...

remove Power Query queries while keeping the query output tables

excel,powerquery
I use power query to load data from external sources into several excel tables. Before sending this excel to a client, I would like to remove all power query queries (M code) while keeping the output/query tables at place. My current workaround is: unload Power Query convert each table to...

Change the color of legends in Charts in power View

powerview,powerbi,powerquery
Is it possible to change the color of the legends in Power View charts? i have been googling for some time now but could find a way. I could make use of KPIs to have an indicator as in this link: http://www.powerpivotpro.com/2012/05/kpis-in-powerpivot-v2/ I want to add similar range for legend...

Problems to extract feed from Facebook group with Power Query

facebook,powerquery
After the upgrade of Graph API to 2.2, I can't extract the feed from the Facebook Group that I'm admin for. I can extract Members, but nothing else. This function worked earlier this year: = Facebook.Graph("https://graph.facebook.com/v2.2/189530894403791/Feed") And the result: This table is empty....

WAAD Authentication with WebAPI OData service consumed by Excel PowerQuery

asp.net-web-api,odata,single-sign-on,azure-active-directory,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 follows: app.UseWindowsAzureActiveDirectoryBearerAuthentication( new WindowsAzureActiveDirectoryBearerAuthenticationOptions { Tenant = ConfigurationManager.AppSettings["ida:Tenant"], TokenValidationParameters = new TokenValidationParameters { ValidAudience...

How to link table row content to source in Power View

excel,powerpivot,powerview,powerbi,powerquery
I am currently able to use Power View to view, filter, and highlight my data. However I haven't figured out a way to link my table rows to the data source (i.e. tables in other tabs of the Excel spreadsheet). so that if I double-click on a row, Excel will...

How to create nested tiles in Power View

excel,powerpivot,powerview,powerbi,powerquery
I am currently able to use the tile feature in Power View to view data much more quickly. However I haven't figured out a way to have nested tiles to further drill down into the relevant data. For example, I want a tile strip at the top of my view...

YYYMMDD Conversion to MM/DD/YYYY in Power QUery

excel,powerquery
I have a column named DimDateKey in PowerQuery and it is formatted as YYYYMMDD i.e. 20150201 and I want to convert it to MM/DD/YY and I have had no luck finding the syntax for this. I have tried this which works in a regular excel sheet: DATE(RIGHT([DimDateKey],4),MID([DimDateKey],4,2),LEFT([DimDateKey],2)) But I get...

How to bulk-import all Power Query workbook connections into PowerPivot?

excel,excel-2010,powerpivot,powerquery,powerbi
Within PowerPivot for Excel, under the 'Design' tab, there's an option to use 'Existing Connections' as a data source for Power Pivot. Here we can select our carefully shaped & managed Power Query connections (e.g. to SQL Server, Web pages, flat files). However this interface only permits the selection of...

Can I load calculated measures from SSAS cube in Power Query?

excel,business-intelligence,msbi,powerquery
Power Query Version 2.17 is supporting SSAS Cubes as data source now. When I connect to a cube I just see the standard measures of the cube and not the calculated members defined in the cube. Is there a way in Power Query to get access to the calculated members?...