ollyhughes1982
Well-known Member
- Joined
- Nov 27, 2018
- Messages
- 718
- Office Version
- 365
- Platform
- 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:
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:
Any help appreciated!
Thanks in advance,
Olly.
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:
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.