Copying excel query to power bi

0
votes

I'm trying to copy excel query into PowerBI query. In excel I have 

let
    Source = Csv.Document(File.Contents(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{2}[Path]),null,{0, 55, 62, 134, 145, 386, 398, 410},null,1200),
    #"Appended Query" = if Date.Month(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{5}[start])=Date.Month(Excel.CurrentWorkbook(){[Name="CurrentDate"]}[Content]{0}[CurrentDate]) then Source else Table.Combine({Source, Csv.Document(File.Contents(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{3}[Path]),null,{0, 55, 62, 134, 145, 386, 398, 410},null,1200)}),

In excel this works all good but in Power BI I get error. ( In excel howering over the query it shows data source: X:...... .txt ) I tried copying the file path into the code but because of the "Date" section I still get errors.

Expression.Error: We couldn't find an Excel table named 'Request'. Details: Request.

2
excelpowerbipowerquery
Changing Excel.CurrentWorkbook(){[Name="Request"] and Excel.CurrentWorkbook(){[Name="Request"]}[Content]{5}[start] with the path of the file gives error: Expression.Error: The Date value must contain the Date component. – user245255

2 Answers

0
votes

The Excel.CurrentWorkbook() part means it's referring to a table within your Excel file. If you are using Power BI, there is no current workbook. If you want to connect to a data table in an Excel file, then it will look more like

Excel.Workbook(
    File.Contents("\filepath\filname.xlsx"),
    null, true){[Item="TableName",Kind="Table"]}[Data]

I recommend using the GUI to connect to sources (Home > New Source > Excel).

0
votes

How to load an Excel sheet named .txt

Problem is that the file is not an excel file but a plain .txt ( I can't change it )

That still works. (Extensions don't actually mean anything, or change the contents of files. it's just like changing the file name.)

let  
    txt_filepath = "C:\Excel\worksheet.txt",
    worksheet_bytes = File.Contents( txt_filepath ),   
    Source = Excel.Workbook(worksheet_bytes, true),    
    FinalTable = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
    FinalTable

WorkBook paramaters (verses functions)

"Excel.CurrentWorkbook part with an actual source path without losing its parameters in the code

You can use Report/Worksheet Parameters. It's easy to change the values, without having to touch the Power Query itself.

enter image description here

You can even make the parameter automatically fill in files.

let
    /*
    this generates files to suggest for report parameter
    */
    Source = Folder_FilesFormatted( Folder.Files( #"Base Path Param" ) ),
    Path_Listing = List.Sort( Source[Full Path] )
in
    Path_Listing

enter image description here