Input variable in pq

[sadath]

Hi

i have made a date input variable as below(query Name DATETABLE which returns correct result eg 10 Apr 2018

let
Source = Excel.CurrentWorkbook(){[Name="DATE"]}[Content],
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"ReportDate", type datetime}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"ReportDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Date1", each Date.ToText([ReportDate],"dd MMM yyyy")),
#"Changed Type" = Table.RemoveColumns(#"Added Custom",{"ReportDate"}),
AddCri = Record.Field(#"Changed Type"{0},"Date1")
in
AddCri


my date file name is C:\Users\sadat\Desktop\HD REPORT\10 Apr 2018.csv

let
AddCri = DATETABLE,
Source = Csv.Document(File.Contents("C:\Users\sadat\Desktop\HD REPORT" & AddCri & ".csv"),[Delimiter=",", Columns=17, Encoding=1200, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"STORECODE", Int64.Type}, {"STORENAME", type text}, {"SALESDT", type datetime}, {"ORDERCODE", Int64.Type}, {"AMOUNT", Int64.Type}, {"TRANSACTIONSTATUS", type text}, {"DELIVERYSTATUS", type text}, {"STATUS", Int64.Type}, {"SUBMISSION_TIME", type time}, {"ASSIGN_TIME", type time}, {"OUT_TIME", type time}, {"CLOSE_TIME", type time}, {"ASSIGN", Int64.Type}, {"OUT", Int64.Type}, {"PARLOUR_TIME", Int64.Type}, {"CLOSE", Int64.Type}, {"DELIVERY_TIME", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([DELIVERYSTATUS] = "D")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"SALESDT", "DELIVERYSTATUS", "STATUS", "ASSIGN_TIME", "ASSIGN", "OUT", "PARLOUR_TIME", "CLOSE", "DELIVERY_TIME"})
in
#"Removed Columns"



this returns wrong...

 

[horseyride]

Assuming this is your real code and not a typo,

Source = Csv.Document(File.Contents("C:\Users\sadat\Desktop\HD REPORT"  & AddCri & ".csv")

should be

Source = Csv.Document(File.Contents("C:\Users\sadat\Desktop\HD REPORT\"  & AddCri & ".csv")

with the extra trailing slash. I didn't check any futher

 

[sadath]

Hi
Still it returns error

I tried below, that is working

let
AddCri = "10 Apr 2018",
Source = Csv.Document(File.Contents("C:\Users\sadat\Desktop\HD REPORT" & AddCri & ".csv"),[Delimiter=",", Columns=17, Encoding=1200, QuoteStyle=QuoteStyle.None]),

but when i change into AddCri = DATETABLE the following error getting

Formula.Firewall: Query 'Query2' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

any one can help

 

[horseyride]

Most simple is to combine into one query

let
Source = Excel.CurrentWorkbook(){[Name="DATE"]}[Content],
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"ReportDate", type datetime}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"ReportDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Date1", each Date.ToText([ReportDate],"dd MMM yyyy")),
#"Changed Type" = Table.RemoveColumns(#"Added Custom",{"ReportDate"}),
AddCri = Record.Field(#"Changed Type"{0},"Date1"),
Source2 = Csv.Document(File.Contents("C:\Users\sadat\Desktop\HD REPORT\" & AddCri & ".csv"),[Delimiter=",", Columns=17, Encoding=1200, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source2, [PromoteAllScalars=true]),
#"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"STORECODE", Int64.Type}, {"STORENAME", type text}, {"SALESDT", type datetime}, {"ORDERCODE", Int64.Type}, {"AMOUNT", Int64.Type}, {"TRANSACTIONSTATUS", type text}, {"DELIVERYSTATUS", type text}, {"STATUS", Int64.Type}, {"SUBMISSION_TIME", type time}, {"ASSIGN_TIME", type time}, {"OUT_TIME", type time}, {"CLOSE_TIME", type time}, {"ASSIGN", Int64.Type}, {"OUT", Int64.Type}, {"PARLOUR_TIME", Int64.Type}, {"CLOSE", Int64.Type}, {"DELIVERY_TIME", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type3", each ([DELIVERYSTATUS] = "D")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"SALESDT", "DELIVERYSTATUS", "STATUS", "ASSIGN_TIME", "ASSIGN", "OUT", "PARLOUR_TIME", "CLOSE", "DELIVERY_TIME"})
in
#"Removed Columns"

 

[sadath]

Hi
Thank You horseyride.. Now its working..