• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

custom column with condition

MBS

Member
Hi All,

I want to Add or Split custom column, with Power Query, which have mixed data type, based on condition that, if entry in cell have “Cr”, then it will go to added column with “-“ Sign.

Please suggest.


Data Type (Mixed) Or TextData Type (decimal)Data Type (decimal)
TransactionDebitCredit
158158
112 Cr-112
164164
193193
103103
248 Cr-248
221221
6969
9090
 
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Debit", each if Text.Contains([Transaction],"Cr") then null else [Transaction]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Credit", each if Text.Contains([Transaction],"Cr") then [Transaction] else null),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom1","Cr","",Replacer.ReplaceText,{"Credit"}),
    #"Added Prefix" = Table.TransformColumns(#"Replaced Value", {{"Credit", each "-" & _, type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Prefix",{{"Debit", type number}, {"Credit", type number}})
in
    #"Changed Type1"
 
alternative approach, with a bit of a risk, but looking for space or "Cr" in the given sample is the same.
there seems to be a way to define the data types inside a record, but that I'm still learning how to accomplish it.
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Transaction],
    Table_from_records = Table.FromRecords
    (
        List.Transform(Source, each
            [Value = try Number.FromText(Text.BeforeDelimiter(_, " ") ) otherwise _,
            Record = [Transaction = _ ,
                        Debit = if _ <> Value then null else Value ,
                        Credit = if Debit = null then - Value else null]
                    ]
        [Record]
        )
    )
in
    Table_from_records
 
Back
Top