Log In
Register
Home
August 21, 2019
Offline
Hi
Any Power Query expert can share with me effective M language codes to transpose Country1/City1/StartDate1/EndDate2........Country2/City2/StartDate12/EndDate2 which come under a Question, basically I need to display them in to multiple rows under same UID and the header is Country/City/StartDate/EndDate
Currently I am using very primitive way to break them then append as new query, my current data records got 4000 records and each refresh took a couple of minutes (See my sample queries)
Appreciate I wound like to learn from you guy any better way to code it to improve performance
Thank you
July 16, 2010
Offline
Hi Chris,
You didn't provide a 'desired result' example, so I've guessed a little. See query 'Table1' and result on 'Sheet1' of the attached file. I hope this points you in the right direction.
Mynda
New Member
February 6, 2020
Offline
Hi
let
Source = Table.FromRows({
{"guid1", "text desc1", "country1", "city1", #date(2019, 1, 1), #date(2019, 2, 1), "country2", "city2", #date(2019, 2, 1), #date(2019, 3, 1), "country3", "city3", #date(2019, 3, 1), #date(2019, 4, 1)},
{"guid2", "text desc2", "country4", "city4", #date(2019, 1, 1), #date(2019, 2, 1), "country5", "city5", #date(2019, 2, 1), #date(2019, 3, 1), "country6", "city6", #date(2019, 3, 1), #date(2019, 4, 1)}
}, {"UID", "DESC", "Country1", "City1", "Start1", "End1", "Country2", "City2", "Start2", "End2", "Country3", "City3", "Start3", "End3"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"UID", "DESC"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each
let
lastDigits = List.LastN(Text.ToList([Attribute]), (item) => List.Contains({"0".."9"}, item))
in
[name = Text.Range([Attribute], 0, Text.Length([Attribute]) - List.Count(lastDigits)), digits = Text.Combine(lastDigits)]),
#"Expanded {0}" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"name", "digits"}, {"name", "digits"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded {0}",{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[name]), "name", "Value")
in
#"Pivoted Column"
Regards,
1 Guest(s)
