Log In
Register
Home
December 15, 2017
Offline
I have created a query initially using PowerQuery, and performed some transformations. Then, in the Data Model, I made some additional changes in order to get the data how it was needed.
Now, I would like to re-use this modified query, but when I open Get Data / From Other Sources / Blank Query / Advanced Editor, and paste the query in, i get an error, "Expression.SyntaxError: Token Eof expected".
How does one re-use a query that has been modified in the Data Model? I cannot paste the work book, but here is the Query that I get when I right click the query, and am trying to paste into Advanced Editor.
let
Source = Folder.Files("FIle Path"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Site", type text}, {"Location", type text}, {"Sub Location", type text}, {"Date of Inspection", type date}, {"Equipment Type (System)", type text}, {"UniFormat Class", type text}, {"OmniClass", type text}, {"Manufacturer", type text}, {"Model", type text}, {"Serial", type text}, {"Asset ID", type any}, {"Install Date", type date}, {"Functioning", type text}, {"Remaining Life", Int64.Type}, {"Condition", type text}, {"Repair Cost", Int64.Type}, {"Inspector Name", type text}, {"Special Considerations", type text}, {"Inspector Notes", type text}, {"Building", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each true),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Asset ID", type text}, {"Install Date", type date}, {"Remaining Life", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each true),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1","poor","Poor",Replacer.ReplaceText,{"Condition"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","good","Good",Replacer.ReplaceText,{"Condition"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","poor","Poor",Replacer.ReplaceText,{"Condition"}),
#"Filtered Rows2" = Table.SelectRows(#"Replaced Value2", each true),
#"Replaced Value3" = Table.ReplaceValue(#"Filtered Rows2","fair","Fair",Replacer.ReplaceText,{"Condition"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","GOOD","Good",Replacer.ReplaceText,{"Condition"}),
#"Filtered Rows3" = Table.SelectRows(#"Replaced Value4", each true),
#"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows3", "Condition", "Condition - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Site", "Location", "Sub Location", "Date of Inspection", "Equipment Type (System)", "UniFormat Class", "OmniClass", "Manufacturer", "Model", "Serial", "Asset ID", "Install Date", "Functioning", "Remaining Life", "Condition", "Condition - Copy", "Repair Cost", "Inspector Name", "Special Considerations", "Inspector Notes", "Building"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Condition - Copy", "Condition Order"}}),
#"Replaced Value5" = Table.ReplaceValue(#"Renamed Columns","Poor","4",Replacer.ReplaceText,{"Condition Order"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Fair","3",Replacer.ReplaceText,{"Condition Order"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Good","2",Replacer.ReplaceText,{"Condition Order"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","N/A","1",Replacer.ReplaceText,{"Condition Order"})
in
#"Replaced Value8"
// Transform File
let
Source = (Parameter1) => let
Source = Excel.Workbook(Parameter1, null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data]
in
Table1_Table
in
Source
// Sample File
let
Source = Folder.Files("File Path"),
Navigation1 = Source{0}[Content]
in
Navigation1
1 Guest(s)
