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!
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.
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 |
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"
Thank you again!
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