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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors