conciliation values

[muttleychess]

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

 

[horseyride]

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

 

[muttleychess]

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

Hi

Sorry, but my english is very bad



I would like to put together possibly equal values

https://i.postimg.cc/4ynnvhzK/diferencas-valor.jpg

 

[horseyride]

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

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"

 

[muttleychess]

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

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