Softwaretrain

/*
    FUNCTION NAME: J_EOMONTH
    VERSION:1.0
    AUTHOR: Mahmoud Bani Asadi
    WEB: www.arshad-hesabdar.ir
    INSTAGRAM: @SoftwareTrain
    DESCRIPTION: Return the serial number of the last Persian day of the month before or after a specific number of months.
    ARGS:
        start_date: is a serial date number that represents the start Persian date.
        months: is the number of months before or after the start_date.

Softwaretrain

let
  Source=Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    CustomColumn = Table.AddColumn(Source, "Answer", each let
          Spl=Text.Split([Sentence]," "),
          lenSpl=List.Transform(Spl,each Text.Length(_)),
          addlen=List.Zip({Spl,lenSpl}),
          tbl=#table({"Sentence","length"},addlen),
          filter=Table.SelectRows(tbl,
              let max = List.Max(tbl[length]) 
              in each 

Softwaretrain

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcxBCoMwEIXhqzyyFgnd9ADdeIc0izGmphA6YWZEvL1Run3fzwvBJbIBC6/YM4mikOHDAqoVndTFITgrGXPn9+b946lolQ7F/rWCJJzuZspocpDCGFe/8jLe8CIjNeHWR/79T+MJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Text" = _t]),
    ReplaceText=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk4sUdJRcgaSsTrRSiUZqUBeCJAE8ZJSE4uAXCcQBeInF+UngxQDqfyUzJzUYrBoen4KUNAdSIJ4BUWVicVAfkFRYiVQPhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [From = _t, To = _t]),



    #"Added Custom" = Table.AddColumn(Source, "Final Text", each let
    Translation = List.Buffer(
        List.Zip(
            Table.ToColumns(ReplaceText)

Softwaretrain

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "JYzBCsIwEAX/JWcLL5um2T1qiSC0lqYRkdBTTUHwA/x8Zb3NzGFKMfFjydV36zuzHooZRRY+Z4jajcXxUTHVpw9+RAdcNZBrT5dhsKTGAnb/xbTvr60qLsAsPbkISRrmewwpIzzULDdTnxsC/SbrFw==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )

Softwaretrain

//SplitAmount
=ArrayFormula(LET(
z,division,
count,ROUNDUP(amount/z,),
a,SCAN(0,count,LAMBDA(x,y,x+y))-count+1,
b,SEQUENCE(SUM(count)),
c,SCAN(0,b,LAMBDA(g,h,XLOOKUP(h,a,array,g))),
e,BYROW(amount,LAMBDA(x,LET(n,z,a,MOD(x,n),b,REPT(n&"-",x/n)&IF(a,a,""),b))),
f,TEXTJOIN(",",,e),
g,TOCOL( ArrayFormula(split(transpose(SPLIT(f,",")),"-")),1),

Softwaretrain

# خواندن جداول ورودی
bank = xl("Bank[#All]", headers=True)
dafater = xl("Dafater[#All]", headers=True)

# ایجاد ستون جدید با ترکیب مبلغ و تاریخ و شماره گذاری در صورت تکراری بودن
bank['ترکیب'] = bank['مبلغ'].astype(str) + '-' + bank['تاریخ'].astype(str) + '-' + bank.groupby(['مبلغ', 'تاریخ']).cumcount().add(1).astype(str)
dafater['ترکیب'] = dafater['مبلغ'].astype(str) + '-' + dafater['تاریخ'].astype(str) + '-' + dafater.groupby(['مبلغ', 'تاریخ']).cumcount().add(1).astype(str)

# مقایسه دو فایل بر اساس ستون ترکیب
result_bank = bank[~bank['ترکیب'].isin(dafater['ترکیب'])]

Softwaretrain

import pandas as pd

# خواندن فایل‌های ورودی
bank = pd.read_excel(r"D:\Personal\Python\Reconcile\bank.xlsx")
dafater = pd.read_excel(r"D:\Personal\Python\Reconcile\dafater.xlsx")

# ایجاد ستون جدید با ترکیب مبلغ و تاریخ و شماره گذاری در صورت تکراری بودن
bank['ترکیب'] = bank['مبلغ'].astype(str) + '-' + bank['تاریخ'].astype(str) + '-' + bank.groupby(['مبلغ', 'تاریخ']).cumcount().add(1).astype(str)
dafater['ترکیب'] = dafater['مبلغ'].astype(str) + '-' + dafater['تاریخ'].astype(str) + '-' + dafater.groupby(['مبلغ', 'تاریخ']).cumcount().add(1).astype(str)