create a new query by using steps other queries in...

noobtopowerbi · ‎11-07-2023

I created a query (1) that retrieves data from a csv file. It has some steps like renaming, removing and adding columns.

Now I want to create allmost the same query, but the new query has to retrieve data from multiple files in a folder. The *.csv files have the same structure as the single file mentioned at first

At first I created a query (2) that retrieves the files from the folder, combined the content and splitted the content by it's delimiter.

After that I created a new blank query and copied the script/text from (2) into the advanced editor.

Then I added some steps from query(1) into the new query. I modified the source of every step when necessary.

I expected to get a new query that excutes all steps, but the new query has only one step named "source", which is the first step and one column containing the copied text from the advanced editor.
It looks like I am missing a step, but cannot discover what that could be.

Any suggestions ?

noobtopowerbi · ‎11-14-2023

Solved !!! I do not know where it has gone wrong but I think this was caused by a wrong method of copying the script. It works now

noobtopowerbi · ‎11-08-2023

Unfortunately I cannot attach files so here is the code of the several queries and some text representing the data I load.

//query loading one file

let

Source = Csv.Document(File.Contents("C:\\Documents\example\file2020A.csv"),[Delimiter="|", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}}),

#"Removed Top Rows" = Table.Skip(#"Changed Type",1),

#"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each ([Column1] = "A")),

#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column2", "renamed2"}, {"Column3", "renamed3"}, {"Column4", "renamed4"}, {"Column5", "renamed5"}, {"Column6", "renamed6"}}),

#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Column1", "renamed2", "renamed3", "renamed4"}),

#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"renamed2", Int64.Type}, {"renamed3", Int64.Type}}),

#"Added Custom" = Table.AddColumn(#"Changed Type1", "Calculated_value", each [renamed3]*10)

in

#"Added Custom"

//query loading multiple files

let

Source = Folder.Files("C:\Documents\example"),

#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "file2")),

#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),

#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),

#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),

#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"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",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}}),

#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Column1] = "A"))

in

#"Filtered Rows1"

//this is the new query I created as a combination of the queries above