Hello
I am trying to get an excel report into powerbi. In excel there are many querys. When I open them in advanced editor and copy the code into powerbi I get errors. For example code in excel:
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)}),
#"Filtered Rows" = Table.SelectRows(#"Appended Query", each ([Column8] <> "" and [Column8] <> "---------")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each true),
#"Trimmed Text" = Table.TransformColumns(#"Filtered Rows1",{ {"Column1", Text.Trim, type text},{"Column2", Text.Trim, type text}, {"Column3", Text.Trim, type text}, {"Column4", Text.Trim, type text}, {"Column5", Text.Trim, type text}, {"Column6", Text.Trim, type text}, {"Column7", Text.Trim, type text}, {"Column8", Text.Trim, type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Trimmed Text", [PromoteAllScalars=true]),
#"Filtered Rows3" = Table.SelectRows(#"Promoted Headers", each ([AlguseKpv] <> "AlguseKpv")),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows3",{{"AlguseKpv", type datetime}, {"Kood", Int64.Type}}),
#"Filtered Rows4" = Table.SelectRows(#"Changed Type1", each true),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows4", each ([TooLopp] <> "(null)")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows2",".",",",Replacer.ReplaceText,{"PaevaLiik"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"TooAlgus", type number}, {"TooLopp", type number}, {"PaevaLiik", type number}, {"AlguseKpv", type date}}),
#"Added BH" = Table.AddColumn(#"Changed Type2", "BH", each #time(Number.IntegerDivide([TooAlgus],1),Number.Mod([TooAlgus],1)*60,0)),
#"Added EH" = Table.AddColumn(#"Added BH", "EH", each #time(if [TooLopp]>23 then 24 else [TooLopp],0,0)),
#"Renamed Columns" = Table.RenameColumns(#"Added EH",{{"Kood", "ID"}, {"Kood1", "CostCenter"}, {"Ametikoht", "Position"}, {"AlguseKpv", "Date"}, {"Nimi", "Name"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Duration", each #time(Number.IntegerDivide(if [TooLopp]>23 then 23-[TooAlgus] else [TooLopp]-[TooAlgus],1),Number.Mod(if [TooLopp]>23 then 23-[TooAlgus] else [TooLopp]-[TooAlgus],1)*60,0)),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"BH", type time}, {"EH", type time}, {"Duration", type time}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"TooAlgus", "TooLopp", "PaevaLiik"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Name", "CostCenter", "Position", "Date", "BH", "EH", "Duration"})
in
#"Reordered Columns"
I know the part "File.Contents(Excel.CurrentWorkbook()" is referring to a workbook that is not in powerbi. Problem is this code above works perfectly well in excel but the file it connects to is not an excel file but a plain .txt file. When i connect through GUI to the same file in powerbi the columns are all messed up. In excel it shows 8 columns but in powerbi it shows only 2 and it doesnt mather which delimiter I use. So I'm guessing inside this code there are some parameters that tell the program how to format this file. ( Sadly I cant change this .txt file into an excel file and make changes inside the excel file. ) My question is how I could replace the "File.Contents(Excel.CurrentWorkbook()" part with an actual file path without loosing any parameters.
So far I have tried replaceing "File.Contents(Excel.CurrentWorkbook()" or even "File.Contents(Excel.CurrentWorkbook(){[Name="Request"]}[Content]{2}[Path]" and "Excel.CurrentWorkbook(){[Name="Request"]}[Content]{5}[start]" with the correct path but then theres an error with date: "Expression.Error: The Date value must contain the Date component."
EDIT: Okay I tried breaking down the query into pieces and got the Source working with:
= Csv.Document(File.Contents("X:\XXXXXXXXXXXX.txt"),null,{0, 55, 62, 134, 145, 386, 398, 410},null,1200)
Now it shows correctly all columns.
Next problem is the "Append query" that uses code:
= 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)})
This one is not that simple since I can see a workbook Name="CurrentDate" but the query shows only 1 data source which is the same 1 file.
EDIT2:
In Append query I tried:
#"Appended Query" = if Date.Month("X:\XXXXXXXXXXXXXX.txt"[Content]{5}[start])=Date.Month("X:\XXXXXXXXXXXXXX.txt"[Content]{0}[CurrentDate]) then Source else Table.Combine({Source, Csv.Document(File.Contents("X:\XXXXXXXXXXXXXX.txt"),null,{0, 55, 62, 134, 145, 386, 398, 410},null,1200)}),
Get error: Expression.Error: We cannot apply field access to the type Text.
Details:
Value=X:\XXXXXXXXXXXXXX.txt
Key=Content
So I can understand from this I cant add [Content] parameter this way to a path but I'm quite sure this parameter needs to be pointed out since in the data the 5th column is the start date and 0 column is the day column. ( Guessing in this append query these 2 columns need to be compared with eachother. )
So if theres someone who could point out how to add these parameters to a path would be great.
