Search code examples
excelpowerbipowerquerym

Editing excel query to work well with powerbi query

I have excel report that needs to be made in powerbi. Now I have 1 more problem with the last query. In excel there is a sheet "Lists" that contains all kinds of random data ( for ex. document paths, calculated dates etc, theyre all pretty randomly ).

In query I have line:

#"Filtered Rows1" = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([#"Date - Time.Year"]) = Text.Combine({"[Time].[Time].[Calendar Year].&[", Number.ToText(Date.Year(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{0}[end])),"]"}) meta [DisplayName = Number.ToText(Date.Year(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{0}[end]))]),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Date - Time.Days", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [#"Date - Time.Days"] >= #date(Date.Year(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{5}[start]),Date.Month(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{5}[start]),Date.Day(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{5}[start])) and [#"Date - Time.Days"] <= #date(Date.Year(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{0}[end]),Date.Month(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{0}[end]),Date.Day(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{0}[end]))),

In that code first row part:

Number.ToText(Date.Year(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{0}[end]))

I've figured out this code connects to the excel file sheet "Lists" ( table from A1 to F7) - something like this:

+--------------+------------------+------------+------------+-----------------+----------+
| NameOfSource |   httpaddress    |   start    |    end     |      Path       |   File   |
+--------------+------------------+------------+------------+-----------------+----------+
| Data 1       | http:....        | 09.03.2020 | 11.03.2020 | http:....       |          |
| Data 2       | http:....        |            |            | http:....       |          |
| Data 3       | X:\....\.. .txt  |            |            | X:\....\.. .txt |          |
| Data 4       | X:\....\.. 2.txt |            |            | X:\....\.. .txt |          |
| Data 5       | X:\....\.. 3.txt | 01.03.2020 | 08.03.2020 | X:\....\.. .txt | xxx.json |
+--------------+------------------+------------+------------+-----------------+----------+

So my guess is the code part [Content]{0} refers to the end column where value is 11.03.2020. Now this 11.03.2020 is calculated with code =DATE(YEAR(NOW());MONTH(NOW());DAY(NOW())-1).

In PowerBI I do not want to use this method how to fetch data, if possible I want this data into the original query code somehow. So instead of Number.ToText(Date.Year(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{0}[end])) I'd like the code to do the same calculations without pointing to another sheet or place.

Same with 

Date.Year(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{5}[start])

Instead of pointing to a sheet row [Content]{5}[start] here uses code =EOMONTH([@end];-1)+1

Hope I explained well enough :)

OR since this is not the only data queries pull from this "Lists" sheet, maybe it is easier to create such a sheet in powerbi aswell? In that case is there any documentation that would explain how to do this? Because I don't think PowerBI uses (Excel.CurrentWorkbook) or something like that.

Solution

  • The power query M language has very nice built-in date functions.

    Instead of DATE(YEAR(NOW());MONTH(NOW());DAY(NOW())-1), you can write

    Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1)

    Instead of EOMONTH([@end];-1)+1, you can write

    Date.StartOfMonth(DateTime.Date(DateTime.LocalNow()))

    It might be cleaner if you first define today's date as

    Today = DateTime.Date(DateTime.LocalNow())

    and then write

    Date.AddDays(Today,-1)

Date.StartOfMonth(Today)