Input variable in pq

sadath

Active Member
Joined
Oct 10, 2004
Messages
262
Office Version
  1. 365
Platform
  1. Windows
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...
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Assuming this is your real code and not a typo,
Code:
Source = Csv.Document(File.Contents("C:\Users\sadat\Desktop\HD REPORT"  & AddCri & ".csv")
should be
Code:
Source = Csv.Document(File.Contents("C:\Users\sadat\Desktop\HD REPORT\"  & AddCri & ".csv")
with the extra trailing slash. I didn't check any futher
 
Last edited:
Upvote 0
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
 
Upvote 0
Most simple is to combine into one query
Code:
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"
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top