none
Add multiple custom columns to a table RRS feed

  • Question

  • Is is possible to add multiple custom columns to a table in a single step? Especially if you're using a similar function?

    Say I have a table "Source" with three columns, A, B, C, and D, and I want to create custom columns like this:

        AddBperA= Table.AddColumn(Source, "B per A", each [B]/[A]),
        AddCperA= Table.AddColumn(AddBperA , "C per A", each [C]/[A]),
        AddDperA= Table.AddColumn(AddCperA , "D per A", each [D]/[A])

    Is there a more elegant/concise way to do this besides adding each custom column one at a time?

    Thursday, August 13, 2015 8:24 PM

Answers

  • sorry, looks like we lost the original columns here. So the code is getting longer... :-(

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        Unpivot = Table.UnpivotOtherColumns(Source, {"A"}, "Attribute", "Value"),
        CalcValueNew = Table.AddColumn(Unpivot, "ValueNew", each [Value]/[A]),
        Unpivot2 = Table.UnpivotOtherColumns(CalcValueNew, {"A", "Attribute"}, "Attribute.1", "Value.1"),
        Clean = Table.ReplaceValue(Unpivot2,"Value","",Replacer.ReplaceText,{"Attribute.1"}),
        Merge = Table.CombineColumns(Clean,{"Attribute.1", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Header"),
        Sort = Table.Sort(Merge,{{"Header", Order.Ascending}}),
        Pivot = Table.Pivot(Sort, List.Distinct(Sort[Header]), "Header", "Value.1", List.Sum)
    in
        Pivot


    Imke

    www.TheBIccountant.com


    Friday, August 14, 2015 7:16 AM
    Moderator

All replies

  • Only way that I know of to create multiple columns in a single step is SplitColumn.

    https://support.office.com/en-us/article/Table-SplitColumn-Applies-to-Power-Query-81fbb1b1-ac01-4715-a6ef-7b8e90b631bb?ui=en-US&rs=en-US&ad=US

    Doubt that will help you here.

    Friday, August 14, 2015 12:01 AM
  • You could define helper to allow this, but I'm not sure it's any more concise:

        Table.AddColumns = (table as table, addedColumns as list) as table =>
            List.Accumulate(
                addedColumns,
                table,
                (t, d) => Table.AddColumn(t, d{0}, d{1}, d{2}?)),
        AddedColumns = Table.AddColumns(Source, {
            { "B per A", each [B]/[A] },
            { "C per A", each [C]/[A] },
            { "D per A", each [D]/[A] }
            })
    

    Friday, August 14, 2015 3:09 AM
  • This could come in handy if you have a dynamic amount of columns that should be divided by that value in Column A:

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        Unpivot = Table.UnpivotOtherColumns(Source, {"A"}, "Attribute", "Value"),
        CalcValueNew = Table.AddColumn(Unpivot, "ValueNew", each [Value]/[A]),
        Remove = Table.RemoveColumns(CalcValueNew,{"Value"}),
        Pivot = Table.Pivot(Remove, List.Distinct(Remove[Attribute]), "Attribute", "ValueNew")
    in
        Pivot

    Could be further improved if running trough a "Table.TransformColumns"-step instead (skip the Remove then), but I couldn't get it working: How to reach out for the column A in the current row? - Does anyone know how to do it?


    Imke

    www.TheBIccountant.com


    Friday, August 14, 2015 6:45 AM
    Moderator
  • sorry, looks like we lost the original columns here. So the code is getting longer... :-(

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        Unpivot = Table.UnpivotOtherColumns(Source, {"A"}, "Attribute", "Value"),
        CalcValueNew = Table.AddColumn(Unpivot, "ValueNew", each [Value]/[A]),
        Unpivot2 = Table.UnpivotOtherColumns(CalcValueNew, {"A", "Attribute"}, "Attribute.1", "Value.1"),
        Clean = Table.ReplaceValue(Unpivot2,"Value","",Replacer.ReplaceText,{"Attribute.1"}),
        Merge = Table.CombineColumns(Clean,{"Attribute.1", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Header"),
        Sort = Table.Sort(Merge,{{"Header", Order.Ascending}}),
        Pivot = Table.Pivot(Sort, List.Distinct(Sort[Header]), "Header", "Value.1", List.Sum)
    in
        Pivot


    Imke

    www.TheBIccountant.com


    Friday, August 14, 2015 7:16 AM
    Moderator
  • Thanks, Imke! I hadn't thought of unpivoting and then pivoting--that's clever!

    This would definitely be helpful if I have, say, columns B through Z to divide by A, and I like that it can handle a dynamic amount of columns. I might wait another day to see if anyone else responds before marking the answer.

    Friday, August 14, 2015 5:34 PM
  • /*
    Tag	ColumnName	Type
    a	Ask	        Currency.Type
    b	Bid	        Currency.Type
    n	Name	        text
    */
    
    let
        Source = Excel.CurrentWorkbook(){[Name="YAHOO_Tags"]}[Content],
        GlobalTable = #table( { }, { { } } ),
        NumTags = Table.RowCount( Source ),
    
        AddColumn = ( Table as table, Index as number ) as table =>
            let
                ColumnsAdded_ = Table.AddColumn( Table, 
                                 Text.From( Source [ColumnName] { Index } ), 
                                 each null,
                                 if ( Source [Type] { Index } = "Currency.Type" ) then Type.ListItem( type { number } ) else Type.ListItem ( type { text } ) ), //Hard code for test only; need all else if... else type any
                ColumnsAdded = if ( Index < NumTags ) then @AddColumn( ColumnsAdded_, Index + 1 ) else Table
            in
                ColumnsAdded,
    
        NewTable = AddColumn( GlobalTable, 0 )
    in
        NewTable
    Dynamic table driven approach sans the columnGenerator; Excel source table in comments at top of code
    Tuesday, December 27, 2016 10:47 AM
  • Hi Imke

    Did you found answer for this ?

    I would also like to add sets of columns in fewer steps 

    Thank you for your help 

    e

    Wednesday, April 8, 2020 2:18 PM
  • Hi Emil,

    yes, I've found a way.

    But I would really like to understand the use cases behind this. So if you tell me your story for which task you want to use it and provide some sample data, I'm happy to provide the solution ;)



    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Friday, April 10, 2020 2:44 PM
    Moderator
  • Hi Imke

    I will try … I have a table with many dates showing some logistics data. I am building lots of the logic in Query Editor so when I load the data into PBI model I can just aggregate instead of build it with dax. I am applying same steps to different scenarios .. For example I measure the duration between order date and dispatch date but the calculation depends on the working week type (mo-fr, su-th, or all days count) which is determined by some other dimension/column. So I am building different scenarios and then creating the final column with if statements.. 

    And to make the code shorter I wanted to combine the creation of colums, for the same final IF statement column, into one step. It would also help if you also know if it has an Impact on performance.

    Thanx a lot

    Friday, April 10, 2020 9:30 PM
  • Hi Imke

    Please can you share the solution 

    Thank you 

    Wednesday, April 15, 2020 8:23 AM
  • Done in two steps:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Record.FromList({[B] / [A], [C] / [A], [D] / [A]} , {"B per A", "C per A", "D per A"})),
        #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"B per A", "C per A", "D per A"}, {"B per A", "C per A", "D per A"})
    in
        #"Expanded Custom"

    Thursday, June 11, 2020 8:45 AM