Forum Discussion
COIL-ibesmond
1 year agoHelper I
Transforming data using Pivot, Unpivot and Transpose, Need to Pivot multiple columns
I've already done a bunch of ETL on this data, but I'm stuck trying to pivot (I think) Value and Type, so it shows Date and Amount as the column headers. I feel pivot is what I need, but you can onl...
- 1 year ago
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], activities = List.Buffer({1..(Table.ColumnCount(Source) - 1) / 2}), tr = List.TransformMany( Table.ToRows(Source), (x) => ((w) => List.Zip( { activities, List.Alternate(w, 1, 1, 1), List.Alternate(w, 1, 1) } ) )(List.Skip(x)), (x, y) => {x{0}} & y ), z = Table.FromRows(tr, {"Person", "Activity", "Date", "Amount"}) in z - 1 year ago
Hi COIL-ibesmond, different approach:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc1RCsAgDAPQu/RbZpNp513E+1/DMWVd2V94JKR3gSRBRma5Q1U9VB+pS8ylLaELuAjqdmoYjtSFscx/Ge8xM8o2+5hta2684naMCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"1st Date" = _t, #"1st Amt" = _t, #"2nd Date" = _t, #"2nd Amt" = _t, #"3rd Date" = _t, #"3rd Amt" = _t, #"4th Date" = _t, #"4th Amt" = _t, #"5th Date" = _t, #"5th Amt" = _t]), Unpivoted = Table.UnpivotOtherColumns(Source, {"Person"}, "Attribute", "Value"), Splitted = Table.SplitColumn(Unpivoted, "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Activity", "Attribute.2"}), Pivoted = Table.Pivot(Splitted, List.Distinct(Splitted[Attribute.2]), "Attribute.2", "Value"), ChangedType = Table.TransformColumnTypes(Pivoted,{{"Person", Int16.Type}, {"Date", type date}, {"Amt", type number}}, "en-US"), SortedRows = Table.Sort(ChangedType,{{"Activity", Order.Ascending}, {"Person", Order.Ascending}}) in SortedRows
Omid_Motamedise
1 year agoSuper User
use this formula
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFdC8IgFIb/ing9UM++3O4GsbsoqLvhRTRjXmwDs+jnZyOYS7dEFOR5H47nNA1mgCPMCRCgECMGJaV2o2pvn3cXI+1VK3036KBbqbGIvpGUUntWvVkDMgJsQ3qS13Fof0OFYw0TnMSTNglrz53SXiZPZ2sQ+FNrPT606bxK3A6ECbJpVTc/Aou2BgBmnYVdK11VLz+SON8PAowSyD6VAkrLeJIeF6N6ysFP5dyd1YxgId4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Value = _t, Type = _t, Activity = _t]),
Custom1 = Table.Split(Source,2),
Custom2 = List.Transform(Custom1,each [Person=_{0}[Person],Date=_{0}[Value],Activity=_{0}[Activity],AMT=_{1}[Value]]),
Custom3 = Table.FromRecords(Custom2)
in
Custom3