Forum Discussion
Solved
Power Query - Pivot Table with Don't aggregate
Hi All, I need help with a problem that I have after importing a .txt, I leave some screenshots below: Actual values Column 1 Values job_name job_name_falsevalue job...
Hi NayTaguada,
You can refer to below formula to group and transform records
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
Hi All,
I need help with a problem that I have after importing a .txt, I leave some screenshots below:
| 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 |
Values were invented as it is confidential data.
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:
List
Does anyone have an idea of how to solve it?
Thanks!!
Nay
Hi NayTaguada,
You can refer to below formula to group and transform records
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