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
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
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
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"
Bookmarks