Power Query seems to be changing column format

ollyhughes1982

Well-known Member
Joined
Nov 27, 2018
Messages
718
Office Version
  1. 365
Platform
  1. MacOS
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:

Screenshot 2023-07-20 at 16.49.00.png


Screenshot 2023-07-20 at 16.49.16.png


Screenshot 2023-07-20 at 16.51.44.png


Screenshot 2023-07-20 at 16.52.06.png


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:

Power Query:
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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?
 
Upvote 0
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
 
Upvote 0
I'd suggest you try just clearing the formatting on the current output table?
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top