Power Query seems to be changing column format

[ollyhughes1982]

Hi,

I have a query where I am bringing data in from a json but some of the formats keep behaving oddly, as per images below:

They are appearing as if they are hyperlinks, for no apparent reason. They should just be normal, as per the other columns. As you can see in the diagrams, the column formats are set correctly, but am getting some strange formatting.

This is my script for it:

let
    Source = Json.Document(Web.Contents("https://images.parkrun.com/events.json")),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{1}[Value],
    #"Converted to Table1" = Record.ToTable(Value),
    Value1 = #"Converted to Table1"{1}[Value],
    #"Converted to Table2" = Table.FromList(Value1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table2", "Column1", {"id", "type", "geometry", "properties"}, {"Column1.id", "Column1.type", "Column1.geometry", "Column1.properties"}),
    #"Expanded Column1.geometry" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.geometry", {"type", "coordinates"}, {"Column1.geometry.type", "Column1.geometry.coordinates"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Column1.geometry", {"Column1.geometry.coordinates", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Expanded Column1.properties" = Table.ExpandRecordColumn(#"Extracted Values", "Column1.properties", {"eventname", "EventLongName", "EventShortName", "LocalisedEventLongName", "countrycode", "seriesid", "EventLocation"}, {"Column1.properties.eventname", "Column1.properties.EventLongName", "Column1.properties.EventShortName", "Column1.properties.LocalisedEventLongName", "Column1.properties.countrycode", "Column1.properties.seriesid", "Column1.properties.EventLocation"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.properties",{"Column1.type", "Column1.geometry.type"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1.id", "Column1.properties.eventname", "Column1.properties.EventLongName", "Column1.properties.EventShortName", "Column1.properties.LocalisedEventLongName", "Column1.properties.countrycode", "Column1.properties.seriesid", "Column1.properties.EventLocation", "Column1.geometry.coordinates"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column1.id", "parkrun ID"}, {"Column1.properties.eventname", "eventname"}, {"Column1.properties.EventLongName", "EventLongName"}, {"Column1.properties.EventShortName", "EventShortName"}, {"Column1.properties.LocalisedEventLongName", "LocalisedEventLongName"}, {"Column1.properties.countrycode", "countrycode"}, {"Column1.properties.seriesid", "seriesid"}, {"Column1.properties.EventLocation", "EventLocation"}, {"Column1.geometry.coordinates", "coordinates"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"parkrun ID", Int64.Type}, {"countrycode", Int64.Type}, {"seriesid", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [seriesid] = 1),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "coordinates", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"coordinates.1", "coordinates.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"coordinates.1", type number}, {"coordinates.2", type number}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"coordinates.1", "Longitude"}, {"coordinates.2", "Latitude"}}),
  #"Reordered columns 1" = Table.ReorderColumns(#"Renamed Columns1", {"parkrun ID", "EventLongName", "EventShortName", "countrycode", "Latitude", "Longitude", "eventname", "LocalisedEventLongName", "seriesid", "EventLocation"}),
  #"Removed columns 1" = Table.RemoveColumns(#"Reordered columns 1", {"LocalisedEventLongName", "seriesid", "EventLocation"}),
  #"Reordered columns 2" = Table.ReorderColumns(#"Removed columns 1", {"parkrun ID", "EventLongName", "EventShortName", "eventname", "countrycode", "Latitude", "Longitude"}),
  #"Renamed columns 1" = Table.RenameColumns(#"Reordered columns 2", {{"parkrun ID", "parkrun ID [parkrun ID #]"}, {"EventLongName", "EventLongName [Event (Venue)]"}, {"EventShortName", "EventShortName [Event (Venue) Short Name]"}, {"countrycode", "countrycode [parkrun Country Code]"}, {"Latitude", "Latitude [Latitude (Decimal Degrees)]"}, {"Longitude", "Longitude [Longitude (Decimal Degrees)]"}}),
  #"Reordered columns 3" = Table.ReorderColumns(#"Renamed columns 1", {"parkrun ID [parkrun ID #]", "EventLongName [Event (Venue)]", "EventShortName [Event (Venue) Short Name]", "countrycode [parkrun Country Code]", "Latitude [Latitude (Decimal Degrees)]", "Longitude [Longitude (Decimal Degrees)]", "eventname"}),
  #"Added custom" = Table.AddColumn(#"Reordered columns 3", "Active Event (Venue)?", each "1"),
  #"Changed column type" = Table.TransformColumnTypes(#"Added custom", {{"Active Event (Venue)?", Int64.Type}}),
  #"Duplicated column" = Table.DuplicateColumn(#"Changed column type", "Active Event (Venue)?", "Active Event (Venue)? - Copy"),
  #"Duplicated column 1" = Table.DuplicateColumn(#"Duplicated column", "Active Event (Venue)? - Copy", "Active Event (Venue)? - Copy - Copy"),
  #"Duplicated column 2" = Table.DuplicateColumn(#"Duplicated column 1", "Active Event (Venue)? - Copy - Copy", "Active Event (Venue)? - Copy - Copy - Copy"),
  #"Renamed columns 2" = Table.RenameColumns(#"Duplicated column 2", {{"Active Event (Venue)? - Copy", "Active Country?"}, {"Active Event (Venue)? - Copy - Copy", "5K Event (Venue)?"}, {"Active Event (Venue)? - Copy - Copy - Copy", "Junior Event (Venue)?"}}),
  #"Reordered columns 4" = Table.ReorderColumns(#"Renamed columns 2", {"parkrun ID [parkrun ID #]", "EventLongName [Event (Venue)]", "EventShortName [Event (Venue) Short Name]", "countrycode [parkrun Country Code]", "Latitude [Latitude (Decimal Degrees)]", "Longitude [Longitude (Decimal Degrees)]", "Active Event (Venue)?", "Active Country?", "5K Event (Venue)?", "Junior Event (Venue)?", "eventname"}),
  #"Replaced value" = Table.ReplaceValue(#"Reordered columns 4", 1, null, Replacer.ReplaceValue, {"Junior Event (Venue)?"})
in
    #"Replaced value"

Any help appreciated!

Thanks in advance,

Olly.

 

[RoryA]

I'm not seeing all the columns in your picture and I'm not getting any hyperlinks in the output. My best guess would be that your sheet columns have become formatted as hyperlinks at some point - perhaps when testing output? If you output to a new sheet, do you have the same problem?

 

[ollyhughes1982]

I'm not seeing all the columns in your picture and I'm not getting any hyperlinks in the output. My best guess would be that your sheet columns have become formatted as hyperlinks at some point - perhaps when testing output? If you output to a new sheet, do you have the same problem?

That’s what I was thinking, and when I do it fresh like you have I have no issues. I just didn’t want to start from scratch again, as I have quite a lot added into the main table, with lookups etc columns added in

 

[RoryA]

I'd suggest you try just clearing the formatting on the current output table?

 

[ollyhughes1982]

Ok, thanks. I’ve checked it an set as general, but haven’t tried changing