Merging Rows where one column has differnet data

[Spike_Craib]

Hopefully someone can help with this query.

I've got data in a format like this

Key	Start Date	End Date	Country
AAA-111	01/08/2019	01/08/2019	UK
AAA-111	01/08/2019	01/08/2019	US
AAA-111	01/08/2019	01/08/2019	HK
BBB-111	02/08/2019	03/08/2019	US
BBB-222	02/08/2019	02/08/2019	US
BBB-222	02/08/2019	02/08/2019	HK

And I want to get it into a format like this

Key	Start Date	End Date	Country
AAA-111	01/08/2019	01/08/2019	UK, US, HK
BBB-111	02/08/2019	03/08/2019	US
BBB-222	02/08/2019	02/08/2019	US, HK

I've tried various ways I can think of but nothing seems to work or work as expected.

Any suggestions?

FYI this all started as data (pulled via API) with the Country coming through as a list and expand to new rows works but extract values doesn't work. I get an error of We cannot convert a value of Type Record to Type Text

[Mariusz · Accepted solution]

Hi @Spike_Craib 

Please follow the steps included in the video.

You can also copy the script below and paste into Advanced Editor of a Blank Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR01DU0NFTSUTIw1Dew0DcyMLRE54R6K8XqEKk0mGilHhBTnZycYEqNkJUaY5oKUmpkZISh1Ig8pSAHxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Key = _t, #"Start Date" = _t, #"End Date" = _t, Country = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Key", "Start Date", "End Date"}, {{"Countrys", each [Country], type list}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Countrys", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"

Or download the attached file for details.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Spike_Craib]

Sorry just getting time to look at this now.

What is 

Binary.FromText("i45WcnR01DU0NFTSUTIw1Dew0DcyMLRE54R6K8XqEKk0mGilHhBTnZycYEqNkJUaY5oKUmpkZISh1Ig8pSAHxAIA", BinaryEncoding.Base64)

Part for/do?

[Mariusz]

Hi @Spike_Craib 

This is how power bi will compress the data once entered as "Enter Data" in query editor

Hope this helps 

Mariusz

[Spike_Craib]

Just tried adding

    #"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Start Date", each List.Min([Start Date]), type text}, {"End Date", each List.Max([End Date]), type text}, {"Country", each Text.Combine(_[Country], ", "), type text}})
in
    #"Grouped Rows"

To my list and I get the following error. 

Expression.Error: We cannot convert a value of type Record to type Table.
Details:
Value=Record
Type=Type

[Mariusz]

Hi @Spike_Craib 

Please can you share more details on what you are doing?

Thanks
Mariusz

[Spike_Craib]

Sure. My source data is coming from an API pull. Here's a simplified version of what's in the Advanced Editor. I'm filtering down to keep the columns simple and only working on one record at a time to keep things simple.

This is he firs time I've worked with pulling data via an API so could easily be doing something wrong.

 let
body = "{""jql"":""issuetype = 'Change Request' AND category = CM_INGI_INFR AND STATUS not in ('Change Implemented', 'Change Withdrawn', 'Change Rejected') AND 'Change Start Date / Time' >= StartOfDay(-2) AND 'Change Start Date / Time' <= StartOfDay(10) "", ""startAt"": 0, ""maxResults"" : 1000, ""fields"":[""fields"", ""description"", ""summary"", ""project"", ""status"", ""customfield_10700"", ""customfield_10702"", ""customfield_10703"", ""customfield_10705"", ""customfield_10608"", ""customfield_10706"", ""created"", ""updated"", ""creator"", ""customfield_11401"", ""customfield_10712"", ""customfield_11400"", ""customfield_13001"", ""customfield_10711"", ""comment""]}",
Source = Json.Document(Web.Contents("https://XXXXXXXXXXXXXXXXXXXXX", [Headers=[#"Authorization"="Basic XXXXXXXXXXXXXXXXXXXXXXXXXXXX", #"Content-Type"="application/json;charset=UTF-8"],
Content = Text.ToBinary(body)
])),
issues = Source[issues],
#"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"expand", "id", "self", "key", "fields"}, {"Column1.expand", "Column1.id", "Column1.self", "Column1.key", "Column1.fields"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.expand", "Column1.id", "Column1.self"}),
#"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Removed Columns", "Column1.fields", {"customfield_10608"}, {"Column1.fields.customfield_10608"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.fields",{{"Column1.fields.customfield_10608", type any}}),
#"Filtered Rows - TEST CHANGE CRQ-1111" = Table.SelectRows(#"Changed Type", each ([Column1.key] = "CRQ-1111")),
#"Rename Key & Country" = Table.RenameColumns(#"Filtered Rows - TEST CHANGE CRQ-1111",{{"Column1.key", "Key"}, {"Column1.fields.customfield_10608", "Country"}}),
#"Create Start Date" = Table.AddColumn(#"Rename Key & Country", "Start Date", each "Start"),
#"Create End Date" = Table.AddColumn(#"Create Start Date", "End Date", each "End"),
#"Reordered Columns" = Table.ReorderColumns(#"Create End Date",{"Key", "Start Date", "End Date", "Country"}),
#"Expand list to new rows" = Table.ExpandListColumn(#"Reordered Columns", "Country"),
Country = Table.ExpandRecordColumn(#"Expand list to new rows", "Country", {"value"}, {"Country.value"}),
#"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Start Date", each List.Min([Start Date]), type text}, {"End Date", each List.Max([End Date]), type text}, {"Country", each Text.Combine(_[Country], ", "), type text}})
in
#"Grouped Rows"

[Mariusz]

Hi @Spike_Craib 

My query is based on data from your original post.
1. You should be able to create a new query and paste it and see the result.
2. If you want to apply the logic to your query, you  need to make sure that the column names are the same or you need to adjust the code.

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.