Log In
Register
Home
December 18, 2018
Offline
Hello, I have been trying desperately to manipulate data from one of our systems, yet it is proving very challenging. I know there are multiple steps to achieving my desired goal, but I'm struggling to use them in the correct order I think.
I've tried grouping, and then creating an index column, and expanding that way, but it's just becoming too confusing. Any help would be greatly appreciated!
See Answer
Trusted Members
October 18, 2018
Online
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column2"}),
#"Filled Up" = Table.FillUp(#"Filled Down",{"Column2"}),
#"Merged Columns" = Table.CombineColumns(#"Filled Up",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([#"Person 2 Shift Samples - Jan 2022_3"] <> null)),
#"Demoted Headers" = Table.DemoteHeaders(#"Filtered Rows"),
#"Transposed Table2" = Table.Transpose(#"Demoted Headers"),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table2", {"Column1", "Column2"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Split Column by Position" = Table.SplitColumn(#"Removed Columns", "Column1", Splitter.SplitTextByPositions({0, 8}, false), {"Column1.1", "Column1.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Value", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Value], "Value") then [Value] else null),
#"Split Column by Character Transition" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Custom.1", "Custom.2"}),
#"Added Custom1" = Table.AddColumn(#"Split Column by Character Transition", "Custom", each if[Custom.2]= null then [Value] else [Custom.2]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Value", "Custom.1", "Custom.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Custom", Int64.Type}})
in
#"Changed Type1"
Note that I needed to change the Value1, Value2, etc to actual values in this solution so that they could be shown in a Pivot Table. Someone with more experience may be able to come up with a shorter version and attain the same results.
December 18, 2018
Offline
Thank you for the time you spent on this! I see a few things in your solution I should have done. With that said, your final solution (the pivot table) is not quite what I'm looking for. I need TMID Test and Cnts columns to be in line with one another.
Is what I'm suggesting not really a possible solution?
| Person 2 | Shift Samples - Jan 2022 | 825 | Value18 | 1 |
Trusted Members
October 18, 2018
Online
Here is the revised code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column2"}),
#"Filled Up" = Table.FillUp(#"Filled Down",{"Column2"}),
#"Merged Columns" = Table.CombineColumns(#"Filled Up",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([#"Person 2 Shift Samples - Jan 2022_3"] <> null)),
#"Demoted Headers" = Table.DemoteHeaders(#"Filtered Rows"),
#"Transposed Table2" = Table.Transpose(#"Demoted Headers"),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table2", {"Column1", "Column2"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Column1", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Removed Columns2" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Columns2",{"Attribute"}),
#"Split Column by Position" = Table.SplitColumn(#"Removed Columns", "Column1.1", Splitter.SplitTextByPositions({0, 8}, false), {"Column1.2", "Column1.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Value", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Value], "Value") then [Value] else null),
#"Split Column by Character Transition" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Custom.1", "Custom.2"}),
#"Added Custom1" = Table.AddColumn(#"Split Column by Character Transition", "Custom", each if[Custom.2]= null then [Value] else [Custom.2]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Value", "Custom.1", "Custom.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Custom", Int64.Type}})
in
#"Changed Type1"
Answers Post
December 18, 2018
Offline
Thank you again!
Moderators
January 31, 2022
Offline
As a variant. No pivot table needed. Result directly from PQ
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Transpose = Table.Transpose(Source),
RemovedColumns = Table.RemoveColumns(Transpose,{"Column3", "Column4"}),
FillDown = Table.FillDown(RemovedColumns,{"Column2"}),
Custom1 = Table.Split (FillDown, 3),
Custom2 = List.Transform ( Custom1, Table.Transpose ),
Custom3 = Table.Combine ( Custom2 ),
Filter = Table.SelectRows(Custom3, each ([Column1] <> null and [Column1] <> "TMID")),
AddNamecolumn = Table.AddColumn(Filter, "Name", each if [Column2] is null and [Column3] is null then [Column1] else null),
AddMonthColumn = Table.AddColumn(AddNamecolumn, "Month", each try if Text.StartsWith ([Column1], "Shift" ) then [Column1] else null otherwise null),
FillDowmNameMonth = Table.FillDown(AddMonthColumn,{"Name", "Month"}),
ChangedType = Table.TransformColumnTypes(FillDowmNameMonth,{{"Column1", type number}, {"Column3", type number}}),
RemovedErrors = Table.RemoveRowsWithErrors(ChangedType, {"Column1"}),
RenamedColumns = Table.RenameColumns(RemovedErrors,{{"Column1", "TMID"}, {"Column2", "Test"}, {"Column3", "Cnts"}}),
ReorderedColumns = Table.ReorderColumns(RenamedColumns,{"Name", "Month", "TMID", "Test", "Cnts"}),
ChangedType1 = Table.TransformColumnTypes(ReorderedColumns,{{"Name", type text}, {"Month", type text}, {"TMID", Int64.Type}, {"Test", type text}, {"Cnts", Int64.Type}})
in
ChangedType1
1 Guest(s)
