none
Data type when adding column RRS feed

  • Question

  • Hi all,

    usually, when I add a column, I make a second step to change the data type of the new column to the required type. Until I found out that the Table.AddColumn function actually has a final argument that lets you decide upon the data type without having to do the additional step. So as a final argument I insert now Int64.Type or type number. When doing so, and I select the column, the data type indicator in my ribbon actually displays 'whole number' or 'decimal number', but the actual data remain outlined to the left (like text). Also, I cannot do any mathematical calculation on it. So all of this implies the new column is actually text, even though the ribbon tells me differently.

    What am I missing here?

    Regards

    Jan

    Wednesday, October 5, 2016 7:21 AM

Answers

  • These two things are semantically different. When you add a type parameter to Table.AddColumn, you're basically asserting that all of the values in the column are of that type. If one isn't, it may lead to a failure down the road. When you change column type with the UI and it inserts a Table.TransformColumnTypes step, it will actually try to transform each individual value in the column to match that type, no matter what its original type was.

    You can still combine the action of Table.TransformColumnTypes into Table.AddColumn, but you may need to change your formula. For instance,

    Step1 = Table.AddColumn(Step0, "NewColumn", each "0"),
    Step2 = Table.TransformColumnTypes(Step1, {{"NewColumn", Int64.Type}})

    combines into

    Step1 = Table.AddColumn(Step0, "New Column", each Int64.From("0"), Int64.Type)

    Wednesday, October 5, 2016 2:23 PM

All replies

  • These two things are semantically different. When you add a type parameter to Table.AddColumn, you're basically asserting that all of the values in the column are of that type. If one isn't, it may lead to a failure down the road. When you change column type with the UI and it inserts a Table.TransformColumnTypes step, it will actually try to transform each individual value in the column to match that type, no matter what its original type was.

    You can still combine the action of Table.TransformColumnTypes into Table.AddColumn, but you may need to change your formula. For instance,

    Step1 = Table.AddColumn(Step0, "NewColumn", each "0"),
    Step2 = Table.TransformColumnTypes(Step1, {{"NewColumn", Int64.Type}})

    combines into

    Step1 = Table.AddColumn(Step0, "New Column", each Int64.From("0"), Int64.Type)

    Wednesday, October 5, 2016 2:23 PM
  • Hi Curt,

    Comments, please on this sightly different approach.  Other attempts would lose the Columns' types.  Also, while we're on the subject,types are lost when loading to Excel.  Known issue?!.  Running trial version Office 365 with 2016 apps but the trial version is really 2013, at least for Excel.  Before you comment on the slim and (incorrect) hard coded default in the transform step, this version is for testing only.  Many thanks.

    let
    
        Source = Excel.CurrentWorkbook(){[Name="AllRootSymbols_IQFEED"]}[Content],
        #"Kept First Rows" = Table.FirstN(Source,5),
        SymbolRowCount = Number.IntegerDivide( #"Counted Rows", 20 ) + (if Number.Mod( #"Counted Rows", 20 ) > 0 then 1 else 0 ),
        PackedSymbolTable_ = Table.FromList({ 1..SymbolRowCount } as list, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Counted Rows" = Table.RowCount(#"Kept First Rows"),
    
        #"Added Custom" = Table.AddColumn(PackedSymbolTable_, "PackedSymbols", each let
        FinalList_ = List.Range( #"Kept First Rows" [Root Symbol], ( [Column1] * 20 - 20 ), 20 ),
        FinalList = Text.Combine( FinalList_, "+" ) as text
    in
        FinalList),
    
        /* Create a tag list from Tags_YAHOO Excel table
    /*
    Tag	ColumnName	Type
    a	Ask	        Currency.Type
    b	Bid	        Currency.Type
    n	Name	        text
    */
    
        YAHOO_Tags = Excel.CurrentWorkbook(){[Name="YAHOO_Tags"]}[Content],
    
        PackedTags = Text.Combine( YAHOO_Tags [ Tag ], null ),
        NumTags = Table.RowCount( YAHOO_Tags ),
    
        RenameColumns = ( Table as table, Index as number ) as table =>
            let
                ColumnTypesTransformed = Table.TransformColumnTypes( Table, { Text.From( "Column" & Text.From( Index + 1) ), if ( YAHOO_Tags [Type] { Index } = "Currency.Type" ) then Type.ListItem( type { Currency.Type } ) else Type.ListItem( type { text } ) } ), 
                ColumnsRenamed_ = Table.RenameColumns( ColumnTypesTransformed, { Text.From( "Column" & Text.From( Index + 1) ), Text.From( YAHOO_Tags [ColumnName] { Index } ) } ), 
                ColumnsRenamed = if ( Index < NumTags ) then @RenameColumns( ColumnsRenamed_, Index + 1 ) else Table
            in
                ColumnsRenamed,
    
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "QuoteTable", each let
                                            Table = GetMultipleQuote_YAHOO( [PackedSymbols], PackedTags )
    in
        Table),
        #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Column1", "PackedSymbols"}),
        #"Expanded QuoteTable" = Table.ExpandTableColumn(#"Removed Columns2", "QuoteTable", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
        #"Renamed Columns" = RenameColumns(#"Expanded QuoteTable", 0 )
    
    in
        #"Renamed Columns"

    Wednesday, December 28, 2016 6:27 PM