conciliation values

muttleychess

New member
Joined
Sep 25, 2017
Messages
9
Reaction score
0
Points
1
Excel Version(s)
2016
Hi

In my sheet have 5 columns

Date
Value
Debit Value
Credit Value
Federative Unit

The Rule is :
01) for Same Federative Unit for each Value Column, I must have a row with a Debit Value and other with Credit Value, the Date can be different

Example
16/02/2018 287,62 287,62 AC
16/02/2018 287,62 287,62 AC

I have a value 287,62 with a debit value and a credit value for same Fedrative unit (AC)

I would like to know what different values

where the credit and debit values ​​may be different

or I can have debit value and have no credit value or vice versa

how can I to found rows with differecnce using power query
 

Attachments

  • study_conciliation3.xlsb
    14.1 KB · Views: 15
It may be just me, but I don't understand the request or the sample that was given
Please attach another file with sample data for the "before" view, and the view that you want to transform into
 

Attachments

  • study_conciliation4.xlsb
    16.6 KB · Views: 12
View attachment Book2.xlsx

You can use either an index or merge query. I used an index to check that Data and Valor match the row directly under, and if so, copy the C from the row beneath. Then formula to compare to D

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type date}, {"Valor", type number}, {"D", type number}, {"C", type number}, {"uf", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if #"Added Index"{[Index]+1}[Data] = [Data] and #"Added Index"{[Index]+1}[Valor] = [Valor] then #"Added Index"{[Index]+1}[C] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([D] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"C", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "C"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "March", each if [D]=[C] then "EQUAL" else "DIFFERENT")
in
    #"Added Custom1"
 
View attachment 9054

You can use either an index or merge query. I used an index to check that Data and Valor match the row directly under, and if so, copy the C from the row beneath. Then formula to compare to D

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type date}, {"Valor", type number}, {"D", type number}, {"C", type number}, {"uf", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if #"Added Index"{[Index]+1}[Data] = [Data] and #"Added Index"{[Index]+1}[Valor] = [Valor] then #"Added Index"{[Index]+1}[C] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([D] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"C", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "C"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "March", each if [D]=[C] then "EQUAL" else "DIFFERENT")
in
    #"Added Custom1"
Very, Very good , thank you
 
Back
Top