Forum Discussion

intrasight's avatar
intrasight
Helper IV
10 years ago

Table columns lose their data types after a Table.AddColumn() statment

I pull in some metrics with the GA connector. Then immediately following, I have this statement to turn ga:yearMonth into a date

 

#"Inserted Date" = Table.AddColumn(#"Added Items", "RecordDate", each Date.From(Text.Middle([Month of Year],0,4) & "-" & Text.Middle([Month of Year],4,2) & "-01"), type date)

 

Before adding that statement, all of the GA metrics columns have correct data types. After adding that statement, all the metric column have switched to type "Any". Am I doing something wrong in my Table.AddColumn() that is causing this?  Is this a Power Query bug? Expected behavior? Hope not since it would really be a pain to have to unecessarily re-type all of the columns in the query.

Need Help

6 Replies

    • intrasight's avatar
      intrasight
      Helper IV
      10 years ago
      let
    Source = GoogleAnalytics.Accounts(),
    #"Account" = Source{[Id="xxx"]}[Data],
    #"Site" = #"Account"{[Id="yyy"]}[Data],
    #"View" = #"Site"{[Id="zzz"]}[Data],
    #"Added Items" = Cube.Transform(#"View", 
       {
          {Cube.AddAndExpandDimensionColumn, "ga:yearMonth", {"ga:yearMonth"}, {"Month of Year"}}, 
          {Cube.AddMeasureColumn, "Pageviews", "ga:pageviews"},
          {Cube.AddMeasureColumn, "Avg. Time on Page", "ga:avgTimeOnPage"}, 
          {Cube.AddMeasureColumn, "Entrances", "ga:entrances"}, 
          {Cube.AddMeasureColumn, "Unique Pageviews", "ga:uniquePageviews"}
       }
    ),
   // parse GAs YYYYMM into a date
   #"Inserted Date" = Table.AddColumn(#"Added Items", "RecordDate", each Date.From(Text.Middle([Month of Year],0,4) & "-" & Text.Middle([Month of Year],4,2) & "-01"), type date)
in
    #"Inserted Date"

      Replace "xxx","yyy","zzz" with your GA IDs.

      Note that after adding the new date column that the types of existing columns are lost.

      Q: why is this formum injecting a "smiley tongue" into my code?

      • Baskar's avatar
        Baskar
        Resident Rockstar
        10 years ago

        What kind of connection you are using ?

        Direct or Import ?

  • davidcox's avatar
    davidcox
    Frequent Visitor
    8 years ago

    The issue also occurs on manually editing the formular bar after introducing a replace values step

  • davidcox's avatar
    davidcox
    Frequent Visitor
    8 years ago

    Hi,

     

    This issue also occurs when manually editing a replace values step in the formula bar. Typically replacing a column with conditional information in another column.

     

    All data types become "any" abc123

     

    Kind Regards

    David