Data Analysis

[alir22456]

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

[v-jingzhan-msft · Accepted solution]

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
    Custom2

Let 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!

[dufoq3]

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
    PivotedColumn

v2

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
    CombinedGeneratedService