Forum Discussion
rafterse
Helper I
1 year agoPower query, move rows to columns based on there value
The data
- Anonymous1 year ago
Hi rafterse
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
ronrsnfld
Super User
1 year agoPerhaps a little different output than what you show.
And the code should adapt to any number of landlines or mobileLines per company.
Data
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lHyzU/KhDAqKioMlWJ1EFI5iXkpOZl5IKYPXNIvPzszEbu+4MTc4tK8dEKSWI3FKW2kFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, #"Phone type" = _t, number = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Phone type", type text}, {"number", type text}}),
//Groub by Company and Phone Type
#"Group Company/Phone" = Table.Group(#"Changed Type",{"Company"},{
{"Numbers", (t)=>
[a=Table.Group(t,"Phone type",{
{"idx", each Table.AddIndexColumn(_,"idx",1)}}),
b=Table.ExpandTableColumn(a,"idx",{"number","idx"}),
c=Table.TransformColumns(b,{"idx", each Text.From(_), type text}),
d=Table.CombineColumns(c,{"Phone type","idx"},Combiner.CombineTextByDelimiter(".",QuoteStyle.None),"Phone type")
][d]}}),
//Expand and Pivot
#"Expanded Numbers" = Table.ExpandTableColumn(#"Group Company/Phone", "Numbers", {"number", "Phone type"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Numbers", List.Distinct(#"Expanded Numbers"[#"Phone type"]), "Phone type", "number"),
#"Set Data Type" = Table.TransformColumnTypes(#"Pivoted Column",
List.Transform(Table.ColumnNames(#"Pivoted Column"), each {_, type text}))
in
#"Set Data Type"
Output