Forum Discussion
Data Analysis
I have a large dataset of customers and the services they bought from my company.
Currently the data is arranged in this format:
Can anyone suggest me a solution in power query editor to trasnform this data format to the below:
Thanks
Hi alir22456
Have a look at my solution as below. It includes four steps.
1. Select Customer column, select Group By in Transform ribbon. Group by Customer column and select All Rows for the new column.
2. Add a custom step in the formula bar with below code. #"Grouped Rows" is the name of previous step.
= Table.TransformColumns(#"Grouped Rows", {{"All Data", each Table.Transpose(Table.SelectColumns(Table.Sort(Table.Distinct(_),{"Service",Order.Ascending}),{"Service"}))}})3. Expand the All Data column from Step 2 result.
4. Add a custom step with below code. This is to change the column names of the expanded columns.
= Table.TransformColumnNames(#"Expanded All Data", each if Text.StartsWith(_, "Column") then Text.Replace(_,"Column","Service ") else _)Full code in Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS0yVNJRCshJzFMwNFGK1cEmbopD3AyHuDmKuBFc3ACHuCEOcSMUcWO4uDEOcUMc4qSaAwyHWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Service = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Service", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"All Data", each _, type table [Customer=nullable text, Service=nullable text]}}), Custom1 = Table.TransformColumns(#"Grouped Rows", {{"All Data", each Table.Transpose(Table.SelectColumns(Table.Sort(Table.Distinct(_),{"Service",Order.Ascending}),{"Service"}))}}), #"Expanded All Data" = Table.ExpandTableColumn(Custom1, "All Data", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}), Custom2 = Table.TransformColumnNames(#"Expanded All Data", each if Text.StartsWith(_, "Column") then Text.Replace(_,"Column","Service ") else _) in Custom2Let me know if you have any questions.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
2 Replies
Hi alir22456, there are many ways:
Result
v1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS0yVNJRCshJzFMwNFGK1cEmbopD3AyHuDmKuBFc3ACHuCEOcSMUcWO4uDEOcUMc4qSaAwyHWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Service = _t]), GroupedRows = Table.Group(Source, {"Customer"}, {{"All", each Table.AddIndexColumn(Table.Sort(Table.Distinct(_, {"Service"}), {{"Service", Order.Ascending}}), "Index", 1, 1, Int64.Type), type table}}), CombinedAll = Table.Combine(GroupedRows[All]), AddedPrefix = Table.TransformColumns(CombinedAll, {{"Index", each "Service " & Text.From(_, "sk-SK"), type text}}), PivotedColumn = Table.Pivot(AddedPrefix, List.Distinct(AddedPrefix[Index]), "Index", "Service", each _{0}?) in PivotedColumnv2
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS0yVNJRCshJzFMwNFGK1cEmbopD3AyHuDmKuBFc3ACHuCEOcSMUcWO4uDEOcUMc4qSaAwyHWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Service = _t]), Fn_GenerateService = (myTable as table)=> [ // _Detail = GroupedRows{[Customer="Customer3"]}[All], _Detail = myTable, _RemovedDuplicates = Table.Distinct(_Detail, {"Service"}), _SortedRows = Table.Sort(_RemovedDuplicates,{{"Service", Order.Ascending}}), _BufferedService = List.Buffer(_SortedRows[Service]), _GeneratedServiceColumns = List.Accumulate( {0..Table.RowCount(_SortedRows)-1}, Table.FirstN(Table.RemoveColumns(_SortedRows, {"Service"}), 1), (s,c)=> Table.AddColumn(s, "Service " & Text.From(c+1), (x)=> _BufferedService{c}, type text)) ][_GeneratedServiceColumns], GroupedRows = Table.Group(Source, {"Customer"}, {{"All", each _, type table}, {"GeneratedService", Fn_GenerateService, type table}}), CombinedGeneratedService = Table.Combine(GroupedRows[GeneratedService]) in CombinedGeneratedServiceHi alir22456
Have a look at my solution as below. It includes four steps.
1. Select Customer column, select Group By in Transform ribbon. Group by Customer column and select All Rows for the new column.
2. Add a custom step in the formula bar with below code. #"Grouped Rows" is the name of previous step.
= Table.TransformColumns(#"Grouped Rows", {{"All Data", each Table.Transpose(Table.SelectColumns(Table.Sort(Table.Distinct(_),{"Service",Order.Ascending}),{"Service"}))}})3. Expand the All Data column from Step 2 result.
4. Add a custom step with below code. This is to change the column names of the expanded columns.
= Table.TransformColumnNames(#"Expanded All Data", each if Text.StartsWith(_, "Column") then Text.Replace(_,"Column","Service ") else _)Full code in Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS0yVNJRCshJzFMwNFGK1cEmbopD3AyHuDmKuBFc3ACHuCEOcSMUcWO4uDEOcUMc4qSaAwyHWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Service = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Service", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"All Data", each _, type table [Customer=nullable text, Service=nullable text]}}), Custom1 = Table.TransformColumns(#"Grouped Rows", {{"All Data", each Table.Transpose(Table.SelectColumns(Table.Sort(Table.Distinct(_),{"Service",Order.Ascending}),{"Service"}))}}), #"Expanded All Data" = Table.ExpandTableColumn(Custom1, "All Data", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}), Custom2 = Table.TransformColumnNames(#"Expanded All Data", each if Text.StartsWith(_, "Column") then Text.Replace(_,"Column","Service ") else _) in Custom2Let me know if you have any questions.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
