Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power query from excel to power bi from advanced editor.

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.

1 REPLY 1
ImkeF
Super User
Super User

Hi @Anonymous 

if you use {5} on a table it works as an index selector for the row (not the column) and will return the 6th (!) row, as Power Query is zero based indexing.

 

Apart from that I find it very difficult to follow your description. Please add some screenshots and format the relevant code a bit to make it more readable.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors