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

[intrasight]

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.

[davidcox]

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

[davidcox]

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

[Baskar]

can u please give me the entire power Query

[intrasight]

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]

What kind of connection you are using ?

Direct or Import ?

[intrasight]

Using the native Google Analytics connector