Power Query - Pivot Table with Don't aggregate

Question

Hi All,&nbsp;&nbsp;I need help with a problem that I have after importing a .txt, I leave some screenshots below:&nbsp;&nbsp;Actual values&nbsp;&nbsp;Column 1Valuesjob_namejob_name_falsevaluejob_typejob_type_falsevalueserverserver_falsevaluedescriptiondescription_falsevalueapplicationapplication_falsevaluejob_namejob_name_falsevalue2folderfolder_falsevaluejob_typejob_type_falsevalue2serverserver_falsevaluedescriptiondescription_falsevalue2applicationapplication_falsevalue2job_namejob_name_falsevalue3&nbsp;&nbsp;Expected Value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;job_namejob_typeserverdescriptionapplicationfolderjob_name_falsevaluejob_type_falsevalueserver_falsevaluedescription_falsevalueapplication_falsevaluenonejob_name_falsevalue2job_type_falsevalue2server_falsevaluedescription_falsevalue2application_falsevalue2folder_falsevaluejob_name_falsevalue3job_type_falsevalueserver_falsevalue2description_falsevalueapplication_falsevalue3folder_falsevalue&nbsp;Values were invented as it is confidential data.&nbsp;When I tried to perform a pivot, the following message appears:Expression.Error: There were too many elements in the enumeration to complete the operation.Details:&nbsp;&nbsp;&nbsp;&nbsp; List&nbsp;Does anyone have an idea of how to solve it?&nbsp;Thanks!!&nbsp;Nay&nbsp;

anonymous · Accepted Answer

Hi&nbsp;NayTaguada,
&nbsp;
You can refer to below formula to group and transform&nbsp;records

&nbsp;
Full query.
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rY9BCoAgEEXv4tqVHUckpprAsBQtodtnWhFSYNDuDfMW/3FOBt3UE4xI6IV1D8qhB7UgETQp82pOZcdccWg92iAkyN8dutZKM0s9Bed25SIYo2QLh3i7nja9z2bR6bXq4qgE37LYX12sOIwVlFVEiA0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, Values = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Values", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column 1"}, {{"Contents", each Text.Combine(_[Values],","), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Contents", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Contents.1", "Contents.2", "Contents.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Contents.1", type text}, {"Contents.2", type text}, {"Contents.3", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"job_name", type text}, {"job_type", type text}, {"server", type text}, {"description", type text}, {"application", type text}, {"folder", type text}})
in
    #"Changed Type2"
Regards,
Xiaoxin Sheng

Forum Discussion

Power Query - Pivot Table with Don't aggregate

2 Replies

Related content

Power Query - Aggregate multiple columns within group by

Expand and Aggregate in Power Query's Merge Queries

Power Query dates

Aggregate data Power Query Editor

Data transform using power query

Actual values

Column 1	Values
job_name	job_name_falsevalue
job_type	job_type_falsevalue
server	server_falsevalue
description	description_falsevalue
application	application_falsevalue
job_name	job_name_falsevalue2
folder	folder_falsevalue
job_type	job_type_falsevalue2
server	server_falsevalue
description	description_falsevalue2
application	application_falsevalue2
job_name	job_name_falsevalue3

Expected Value

job_name	job_type	server	description	application	folder
job_name_falsevalue	job_type_falsevalue	server_falsevalue	description_falsevalue	application_falsevalue	none
job_name_falsevalue2	job_type_falsevalue2	server_falsevalue	description_falsevalue2	application_falsevalue2	folder_falsevalue
job_name_falsevalue3	job_type_falsevalue	server_falsevalue2	description_falsevalue	application_falsevalue3	folder_falsevalue