none
Combining Multiple steps into one function that returns the result directly in the column, wihtout creating a new temporary custom column. RRS feed

  • Question

  •  

    I do have to replace the "." with a ",", then, using Locale... change the type and then add a "-" if the ammount is Credit, then reorder the columns, then remove the "old" column and then rename the created custom column.

    How do I write a function that does it all, without creating the custom column

       #"Expanded vat" = Table.ExpandTableColumn(#"Expanded trLine", "vat", {"vatID", "vatPerc", "vatAmnt", "vatAmntTp"}, {"vatID", "vatPerc", "vatAmnt", "vatAmntTp"}),
        #"Replaced Value" = Table.ReplaceValue(#"Expanded vat",".",",",Replacer.ReplaceText,{"amnt"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"amnt", Currency.Type}}, "nl-NL"),
        #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "amnt.1", each if [amntTp] = "C" then [amnt]*-1 else [amnt] ),
        #"Reordered Columns2" = Table.ReorderColumns(#"Added Conditional Column",{"Index", "trLine-transaction", "nr", "accID", "docRef", "effDate", "amnt.1", "amnt", "amntTp", "vatID", "vatPerc", "vatAmnt", "vatAmntTp", "desc", "custSupID", "invRef", "costID", "currency", "projID", "prodID"}),
        #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns2",{"amnt"}),
        #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns", {{"amnt.1", Currency.Type}}, "nl-NL"),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"amnt.1", "amnt"}}),

    Saturday, December 31, 2016 2:34 PM

Answers

  • Marcel,

    Your first presented code is far better than the second presented code, which does nothing except defeat the purpose of tracking individual steps (where you can see what's happening for each transformation, and which allows you to modify, replace or add new steps). Moreover, the nesting only hides the viewing of the steps in the query editor - it does not solve the problem of avoiding adding the custom column in the first place.

    While I believe that adding a custom column is the simplest solution, doing so can be avoided. It's up to the user to decide whether the additional code complexity required to avoid adding a custom column is worth the effort, and any potential performance penalties. 

    My approach to the problem is to separate the requirements into two elements:
    1) The first element is to create a custom function that transforms the values in the amnt column (changes the decimal separator and adds the minus sign, if required). The custom function allows you to isolate specific logic from the main transformation code, allows you to add additional complexity at any future time without affecting any other code, and so on.
    2) Manipulate the main code to modify the amnt column without adding a custom column.

    The custom function is called ChangedNumber:

    (value as number, amountType as text) =>
    let
        CheckedValidAmountType = try if List.Contains({"C","D"}, amountType) then amountType 
                                   else error "Amount Type must be C or D",
        ChangedDecimalSeparator = Text.Replace(Number.ToText(value), ".", ","),
        AddedMinus = if CheckedValidAmountType[HasError] then CheckedValidAmountType[Error][Message]
                     else if CheckedValidAmountType[Value] = "C" then 
                            "-"&ChangedDecimalSeparator 
                          else ChangedDecimalSeparator
    in
       AddedMinus

    The code includes a test for a valid Amount Type code. The point though is that you can modify the function to add or remove any level of complexity. If a custom column were acceptable, all you would have to do is add the following expression to the new column = ChangedNumber([amnt], [amtTp]) and then change the column type to a number with the "nl-NL" locale.

    The code without the custom column would be as follows:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"amnt", type number}}),
        TransformedColumn = Table.FromRecords(
                                Table.TransformRows(
                                    ChangedType, 
                                    (r) => Record.TransformFields(
                                                r,
                                                {"amnt", each ConvertedNumber(r[amnt], r[amtTp])}
                                            )
                                 )
                             ),
        ChangedTypeWithLocale = Table.TransformColumnTypes(TransformedColumn, {{"amnt", type number}}, "nl-NL")
    in
        ChangedTypeWithLocale

    If the amnt column didn't need to reference another column in the table, we could use the simpler Table.TransformColumns function. However, Table.TransformColumns doesn't allow a column transformation to reference other columns in the table. Therefore, we have to convert the table to records, work at the record level, and then reassemble the table from the records!!



    Sunday, January 1, 2017 5:41 PM

All replies

  • Hi Robert,

    Why would you want  to avoid a temporary custom column when you end up with the original columns?

    I think it will be too complicated (or even impossible?) to do without custom column.

    Otherwise I created the code below with adjusted type conversions: you can replace decimal points by decimal commas by specifying the original culture of your source data (I used "en-US") rather than using an explicit replace step.

    let
        Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Technet Power Query\Combining multiple steps.xlsx"), null, true),
        Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
        Typed = Table.TransformColumnTypes(Table1_Table, {{"amnt", Currency.Type},{"amntTp", type text}}, "en-US"),
        AddedCustom = Table.AddColumn(Typed, "Custom", each if [amntTp] = "C" then -1 * [amnt] else [amnt], Currency.Type),
        RemovedAmnt = Table.RemoveColumns(AddedCustom,{"amnt"}),
        RenamedCustom = Table.RenameColumns(RemovedAmnt,{{"Custom", "amnt"}}),
        ReorderedColumns = Table.ReorderColumns(RenamedCustom,{"amnt", "amntTp"})
    in
        ReorderedColumns

    An option might be to combine steps by substituting step names in formulas by the code of that step.

    This way, you won't see the temporary custom column when stepping through your code in the Query Editor. The code will be harder to read though.

    Doing this with the code above, results in the following code:

    let
        Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Technet Power Query\Combining multiple steps.xlsx"), null, true),
        Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
        Typed = Table.TransformColumnTypes(Table1_Table, {{"amnt", Currency.Type},{"amntTp", type text}}, "en-US"),
        Result = Table.ReorderColumns(Table.RenameColumns(Table.RemoveColumns(Table.AddColumn(Typed, "Custom", each if [amntTp] = "C" then -1 * [amnt] else [amnt], Currency.Type),{"amnt"}),{{"Custom", "amnt"}}),{"amnt",
     "amntTp"})
    in
        Result






    • Edited by MarcelBeug Sunday, January 1, 2017 6:57 AM
    Sunday, January 1, 2017 6:51 AM
  • Marcel,

    Your first presented code is far better than the second presented code, which does nothing except defeat the purpose of tracking individual steps (where you can see what's happening for each transformation, and which allows you to modify, replace or add new steps). Moreover, the nesting only hides the viewing of the steps in the query editor - it does not solve the problem of avoiding adding the custom column in the first place.

    While I believe that adding a custom column is the simplest solution, doing so can be avoided. It's up to the user to decide whether the additional code complexity required to avoid adding a custom column is worth the effort, and any potential performance penalties. 

    My approach to the problem is to separate the requirements into two elements:
    1) The first element is to create a custom function that transforms the values in the amnt column (changes the decimal separator and adds the minus sign, if required). The custom function allows you to isolate specific logic from the main transformation code, allows you to add additional complexity at any future time without affecting any other code, and so on.
    2) Manipulate the main code to modify the amnt column without adding a custom column.

    The custom function is called ChangedNumber:

    (value as number, amountType as text) =>
    let
        CheckedValidAmountType = try if List.Contains({"C","D"}, amountType) then amountType 
                                   else error "Amount Type must be C or D",
        ChangedDecimalSeparator = Text.Replace(Number.ToText(value), ".", ","),
        AddedMinus = if CheckedValidAmountType[HasError] then CheckedValidAmountType[Error][Message]
                     else if CheckedValidAmountType[Value] = "C" then 
                            "-"&ChangedDecimalSeparator 
                          else ChangedDecimalSeparator
    in
       AddedMinus

    The code includes a test for a valid Amount Type code. The point though is that you can modify the function to add or remove any level of complexity. If a custom column were acceptable, all you would have to do is add the following expression to the new column = ChangedNumber([amnt], [amtTp]) and then change the column type to a number with the "nl-NL" locale.

    The code without the custom column would be as follows:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"amnt", type number}}),
        TransformedColumn = Table.FromRecords(
                                Table.TransformRows(
                                    ChangedType, 
                                    (r) => Record.TransformFields(
                                                r,
                                                {"amnt", each ConvertedNumber(r[amnt], r[amtTp])}
                                            )
                                 )
                             ),
        ChangedTypeWithLocale = Table.TransformColumnTypes(TransformedColumn, {{"amnt", type number}}, "nl-NL")
    in
        ChangedTypeWithLocale

    If the amnt column didn't need to reference another column in the table, we could use the simpler Table.TransformColumns function. However, Table.TransformColumns doesn't allow a column transformation to reference other columns in the table. Therefore, we have to convert the table to records, work at the record level, and then reassemble the table from the records!!



    Sunday, January 1, 2017 5:41 PM