link the Date to the advanced editor of Excel Power Query

Mohamedazees

New Member
Joined
Oct 18, 2020
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
Good Evening,

Sir, I am trying to Link the date from My excel Table to the Power Query Advance editor, I herewith Attached the Complete content of the Advance Editor, i want to link the from date and to date from My Excel table


"
/*
Power Query: Trial Balance (for the period)

Input Parameter:
fromDate [date]: FROM date
toDate [date]: TO date

Output Fields: (as below)
Name [text]: Ledger name
Parent [text]: Group name
PrimaryGroup [text]: Ultimate Primary Group as defined in Tally
OpeningBalance [number]: Opening Balance of Ledger
Net Debit [number]: Sum of debits to ledger row
Net Credit [number]: Sum of credits to ledger row
ClosingBalance [number]: Closing Balance of Ledger
Description [text]: Optional description of ledger

Opening / Closing Balance: (Negative = Dr) | (Positive = Credit)
Net Debit / Credit: Always positive

*/
let
url = "http://localhost:9000",
_body = "<?xml version=""1.0"" encoding=""utf-8""?><ENVELOPE><HEADER><VERSION>1</VERSION><TALLYREQUEST>Export</TALLYREQUEST><TYPE>Data</TYPE><ID>MyReportTrialBalanceForPeriod</ID></HEADER><BODY><DESC><STATICVARIABLES><SVFROMDATE>$fromDate$</SVFROMDATE><SVTODATE>$toDate$</SVTODATE><SVEXPORTFORMAT>ASCII (Comma Delimited)</SVEXPORTFORMAT></STATICVARIABLES><TDL><TDLMESSAGE><REPORT NAME=""MyReportTrialBalanceForPeriod""><FORMS>MyForm</FORMS></REPORT><FORM NAME=""MyForm""><PARTS>MyPart</PARTS></FORM><PART NAME=""MyPart""><LINES>MyLine</LINES><REPEAT>MyLine : MyCollection</REPEAT><SCROLLED>Vertical</SCROLLED></PART><LINE NAME=""MyLine""><FIELDS>FldName,FldParent,FldPrimaryGroup,FldOpeningBalance,FldNetDebit,FldNetCredit,FldClosingBalance,FldDescription</FIELDS></LINE><FIELD NAME=""FldName""><SET>$$StringFindAndReplace:$Name:'""':'""""'</SET></FIELD><FIELD NAME=""FldParent""><SET>$$StringFindAndReplace:$Parent:'""':'""""'</SET></FIELD><FIELD NAME=""FldPrimaryGroup""><SET>if $$IsEmpty:$_PrimaryGroup then $$StrByCharCode:245 else $$StringFindAndReplace:$_PrimaryGroup:'""':'""""'</SET></FIELD><FIELD NAME=""FldOpeningBalance""><SET>$$StringFindAndReplace:(if $$IsDebit:$OpeningBalance then -$$NumValue:$OpeningBalance else $$NumValue:$OpeningBalance):""(-)"":""-""</SET></FIELD><FIELD NAME=""FldNetDebit""><SET>$$Number:$DebitTotals</SET></FIELD><FIELD NAME=""FldNetCredit""><SET>$$Number:$CreditTotals</SET></FIELD><FIELD NAME=""FldClosingBalance""><SET>$$StringFindAndReplace:(if $$IsDebit:$ClosingBalance then -$$NumValue:$ClosingBalance else $$NumValue:$ClosingBalance):""(-)"":""-""</SET></FIELD><FIELD NAME=""FldDescription""><SET>if $$IsEmpty:$Description then $$StrByCharCode:245 else $$StringFindAndReplace:$Description:'""':'""""'</SET></FIELD><COLLECTION NAME=""MyCollection""><TYPE>Ledger</TYPE></COLLECTION></TDLMESSAGE></TDL></DESC></BODY></ENVELOPE>",
fromDate = Date.ToText(#date(2022,4,1), "yyyyMMdd"), /* FROM date */
toDate = Date.ToText(#date(2023,3,31), "yyyyMMdd"), /* TO date */

body = Text.Replace(Text.Replace(_body, "$fromDate$", fromDate), "$toDate$", toDate),
Source = Csv.Document(Text.Replace(Text.FromBinary(Web.Contents(url, [Content=Text.ToBinary(body, TextEncoding.Utf16), Headers=[#"Content-Type"="text/xml;charset=utf-16"]]), TextEncoding.Utf16), ",#(cr,lf)", "#(cr,lf)"), { "Name", "Parent", "PrimaryGroup", "OpeningBalance", "Net Debit", "Net Credit", "ClosingBalance", "Description" }),
Tbl01 = Table.TransformColumns(Source, { { "PrimaryGroup", each Text.Replace(_,"õ","") }, {"OpeningBalance", each Number.FromText(if _ = "" then "0.00" else Text.Replace(Text.Replace(_,",",""),"(-)","-")) as number}, {"ClosingBalance", each Number.FromText(if _ = "" then "0.00" else Text.Replace(Text.Replace(_,",",""),"(-)","-")) as number}, { "Description", each Text.Replace(_,"õ","") } }),
Tbl02 = Table.TransformColumnTypes(Tbl01,{{"OpeningBalance", type number}, {"ClosingBalance", type number}, {"Net Debit", type number}, {"Net Credit", type number}}),
#"Renamed Columns" = Table.RenameColumns(Tbl02,{{"Name", "Name of Ledger as per Tally"}, {"PrimaryGroup", "Tally Grouping (Primary)"}, {"OpeningBalance", "Amount Previous Year"}, {"ClosingBalance", "Amount Current Year"}, {"Parent", "Tally Grouping (Parent)"}})
in
#"Renamed Columns"
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
This is how you pull named ranges from your Excel sheet. At least, if this is what you meant.

Power Query:
xdate =  Excel.CurrentWorkbook(){[Name="testDate"]}[Content][Column1]{0}
 
Upvote 0
Thanks for your prompt response, however, I wouldn't understand therefore now I am attaching the Image showing my Excel table having the Start date and the end date and the Table Named as TB_Period.
 

Attachments

  • Date Table.png
    Date Table.png
    2.2 KB · Views: 6
Upvote 0
Then load the table as parameter table. Afterwards, you can easily refer to the two table values(dates)
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

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