Results 1 to 5 of 5

Thread: conciliation values

  1. #1
    Seeker muttleychess's Avatar
    Join Date
    Sep 2017
    Posts
    8
    Articles
    0
    Excel Version
    2016

    Question conciliation values



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    71
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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

  3. #3
    Seeker muttleychess's Avatar
    Join Date
    Sep 2017
    Posts
    8
    Articles
    0
    Excel Version
    2016

    Unhappy

    Quote Originally Posted by horseyride View Post
    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


    Attached Files Attached Files

  4. #4
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    71
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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"

  5. #5
    Seeker muttleychess's Avatar
    Join Date
    Sep 2017
    Posts
    8
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by horseyride View Post
    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"
    Very, Very good , thank you

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •