Power Query - Split row (with many columns)

C

CountryBoy_71

New Member
Joined
Nov 24, 2023
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello all,

I am hoping that, even though this is against everything I have ever been taught about cleaning and presenting data, there is a PQ solution to my problem. I have a table that contains a total of 18 columns.
1. The first four columns include; Region, Name, File, DateTime1.
2. The next seven include various labeled DateTimes
3. The last seven include various calculated time fields.

I want to split each, single row, in such a way that it looks something like this...I've truncated to fit.
Column1Column2Column3Column4Column5Column6Column7Column8Column9Column9
Column1Column2Column3Column4Column10Column11Column12Column13Column14Column15

I hope that I have made this make sense...even though I shake my head at those wanting it this way!!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Suppose we start with a small table like this, named Table1:
Book1
ABCDEFGHIJKLMNOPQR
1RegionNameFileDateTime1DateTime17DateTime27DateTime37DateTime47DateTime57DateTime67DateTime77Time1Time2Time3Time4Time5Time6Time7
2NorthJohnreportJ1/2/2024 7:501/3/2024 7:501/4/2024 7:501/5/2024 7:501/6/2024 7:501/7/2024 7:501/8/2024 7:501/9/2024 7:509:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM
3SouthPaulreportP1/2/2024 7:501/3/2024 7:501/4/2024 7:501/5/2024 7:501/6/2024 7:501/7/2024 7:501/8/2024 7:501/9/2024 7:509:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM
4EastGeorgereportG1/2/2024 7:501/3/2024 7:501/4/2024 7:501/5/2024 7:501/6/2024 7:501/7/2024 7:501/8/2024 7:501/9/2024 7:509:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM
5WestRingoreportR1/2/2024 7:501/3/2024 7:501/4/2024 7:501/5/2024 7:501/6/2024 7:501/7/2024 7:501/8/2024 7:501/9/2024 7:509:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM
Sheet1

One approach is to create two queries in Power Query: one called Table1, the other called Table1 (2)...the default names given by PQ. For both of these queries, the initial Source step is exactly the same. They both take the Excel table named Table1 as their source (I actually just created the first one, paused after the first Source creation step, and then right clicked on the query name in the left-side PQ editor and selected Duplicate to generate the Table1 (2) query). The Table1 (2) query then looks like this:
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"DateTime17", "DateTime27", "DateTime37", "DateTime47", "DateTime57", "Time5", "Time6", "Time7"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 2, 2, Int64.Type),
    #"Demoted Headers" = Table.DemoteHeaders(#"Added Index"),
    #"Removed Top Rows" = Table.Skip(#"Demoted Headers",1)
in
    #"Removed Top Rows"
Note that I've eliminated the original column headings, so you will need to add those back or add additional PQ steps to preserve them and reapply them. For this Table1 (2) query, the basic approach is to delete any columns that do not belong to the 2nd wrapped row for each of the original table's rows and then add an index of even numbers (2,4,6,...).
For the Table1 query, we do something similar: delete any columns that do not belong to the 1st of the wrapped rows for each of the original table's rows, and then we add an index of odd numbers (1,3,5,...). Then the Table1 (2) query is Appended to the Table1 query and the resultant query is Sorted based on the index column.
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"DateTime67", "DateTime77", "Time1", "Time2", "Time3", "Time4", "Time5", "Time6", "Time7"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "DateTime57", "DateTime57 - Copy"),
    #"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 1, 2, Int64.Type),
    #"Demoted Headers" = Table.DemoteHeaders(#"Added Index"),
    #"Removed Top Rows" = Table.Skip(#"Demoted Headers",1),
    #"Appended Query" = Table.Combine({#"Removed Top Rows", #"Table1 (2)"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Column11", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Column11"})
in
    #"Removed Columns1"
The final result loaded back into Excel:
Book1
ABCDEFGHIJ
2NorthJohnreportJ1/2/24 7:50 AM1/3/24 7:50 AM1/4/24 7:50 AM1/5/24 7:50 AM1/6/24 7:50 AM1/7/24 7:50 AM1/7/24 7:50 AM
3NorthJohnreportJ1/2/24 7:50 AM1/8/24 7:50 AM1/9/24 7:50 AM1/0/00 9:00 AM1/0/00 10:00 AM1/0/00 11:00 AM1/0/00 12:00 PM
4SouthPaulreportP1/2/24 7:50 AM1/3/24 7:50 AM1/4/24 7:50 AM1/5/24 7:50 AM1/6/24 7:50 AM1/7/24 7:50 AM1/7/24 7:50 AM
5SouthPaulreportP1/2/24 7:50 AM1/8/24 7:50 AM1/9/24 7:50 AM1/0/00 9:00 AM1/0/00 10:00 AM1/0/00 11:00 AM1/0/00 12:00 PM
6EastGeorgereportG1/2/24 7:50 AM1/3/24 7:50 AM1/4/24 7:50 AM1/5/24 7:50 AM1/6/24 7:50 AM1/7/24 7:50 AM1/7/24 7:50 AM
7EastGeorgereportG1/2/24 7:50 AM1/8/24 7:50 AM1/9/24 7:50 AM1/0/00 9:00 AM1/0/00 10:00 AM1/0/00 11:00 AM1/0/00 12:00 PM
8WestRingoreportR1/2/24 7:50 AM1/3/24 7:50 AM1/4/24 7:50 AM1/5/24 7:50 AM1/6/24 7:50 AM1/7/24 7:50 AM1/7/24 7:50 AM
9WestRingoreportR1/2/24 7:50 AM1/8/24 7:50 AM1/9/24 7:50 AM1/0/00 9:00 AM1/0/00 10:00 AM1/0/00 11:00 AM1/0/00 12:00 PM
Table1

One obvious issue is this produces wrapped rows with Date/Time data situated above Time-Only data, so the final representation will require some attention.
 
Upvote 0
Using the data from @KRice
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    Auto_types = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Name", type text}, {"File", type text}, {"DateTime1", type datetime}, {"DateTime17", type datetime}, {"DateTime27", type datetime}, {"DateTime37", type datetime}, {"DateTime47", type datetime}, {"DateTime57", type datetime}, {"DateTime67", type datetime}, {"DateTime77", type datetime}, {"Time1", type number}, {"Time2", type number}, {"Time3", type number}, {"Time4", type number}, {"Time5", type number}, {"Time6", type number}, {"Time7", type number}}),
    ColNames = List.Buffer(Table.ColumnNames(Auto_types)),
    base_table = Table.AddIndexColumn(Table.SelectColumns(Auto_types, List.FirstN(ColNames,4)), "Index", 0, 1, Int64.Type),
    Repeat_table = Table.AddIndexColumn(Table.Sort(Table.Repeat(base_table,2),{"Index", Order.Ascending}),"Indx", 0,1),
    stack_cols = Table.AddIndexColumn(Table.FromRows(List.Split(List.Combine(Table.ToRows(Table.SelectColumns(Auto_types,List.LastN(ColNames,14)))),7)), "Index", 0, 1, Int64.Type),
    Merge_steps = Table.NestedJoin(Repeat_table, {"Indx"}, stack_cols, {"Index"}, "stack_cols", JoinKind.LeftOuter),
    #"Expanded stack_cols" = Table.ExpandTableColumn(Merge_steps, "stack_cols", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
    #"Sorted Rows" = Table.Sort(#"Expanded stack_cols",{{"Indx", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Indx"})
in
    #"Removed Columns"

RegionNameFileDateTime1Column1Column2Column3Column4Column5Column6Column7
NorthJohnreportJ1/02/2024 7:501/03/2024 7:501/04/2024 7:501/05/2024 7:501/06/2024 7:501/07/2024 7:501/08/2024 7:501/09/2024 7:50
NorthJohnreportJ1/02/2024 7:509:0010:0011:0012:0013:0014:0015:00
SouthPaulreportP1/02/2024 7:501/03/2024 7:501/04/2024 7:501/05/2024 7:501/06/2024 7:501/07/2024 7:501/08/2024 7:501/09/2024 7:50
SouthPaulreportP1/02/2024 7:509:0010:0011:0012:0013:0014:0015:00
EastGeorgereportG1/02/2024 7:501/03/2024 7:501/04/2024 7:501/05/2024 7:501/06/2024 7:501/07/2024 7:501/08/2024 7:501/09/2024 7:50
EastGeorgereportG1/02/2024 7:509:0010:0011:0012:0013:0014:0015:00
WestRingoreportR1/02/2024 7:501/03/2024 7:501/04/2024 7:501/05/2024 7:501/06/2024 7:501/07/2024 7:501/08/2024 7:501/09/2024 7:50
WestRingoreportR1/02/2024 7:509:0010:0011:0012:0013:0014:0015:00
 
Upvote 0
@CountryBoy_71, please check these results to confirm that the desired columns are being stacked. You mentioned a total of 18 columns, yet your example shows only columns 1-15 and column 9 is repeated twice in the 1st row of each group...so I assumed the last three should be deleted. I believe @GraH has retained the last three columns and did not repeat column 9...I suspect that is what you want, and the output seems to make more sense, with date/times on the 1st row and times only stacked beneath on the 2nd row.
 
Upvote 0
Thank you @KRice and @GraH I will give this a shot today and see if it garners the results I was after. The latter results are exactly what I was hoping to accomplish.
 
Upvote 0
Here one more to try

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cTypes = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Name", type text}, {"File", type text}, {"DateTime1", type datetime}, {"DateTime17", type datetime}, {"DateTime27", type datetime}, {"DateTime37", type datetime}, {"DateTime47", type datetime}, {"DateTime57", type datetime}, {"DateTime67", type datetime}, {"DateTime77", type datetime}, {"Time1", type time}, {"Time2", type time}, {"Time3", type time}, {"Time4", type time}, {"Time5", type time}, {"Time6", type time}, {"Time7", type time}}),
    cols = Table.ColumnNames(cTypes),
    newCols = List.Transform({"1".."7"}, (x)=> "COL" & x),
    tbl = Table.SelectColumns(cTypes,List.FirstN(cols,11)),
    tblDates = Table.RenameColumns(tbl, List.Zip({List.LastN(Table.ColumnNames(tbl),7), newCols})),
    tblTimes = Table.RenameColumns(Table.RemoveColumns(cTypes, List.Skip(Table.ColumnNames(tbl),4)), List.Zip({List.LastN(cols,7), newCols})),
    result = Table.Sort(Table.Combine({tblDates,tblTimes}),{"Region", Order.Ascending})
in
    result

Book1
ABCDEFGHIJK
8
9RegionNameFileDateTime1COL1COL2COL3COL4COL5COL6COL7
10EastGeorgereportG2-1-2024 07:503-1-20244-1-20245-1-20246-1-20247-1-20248-1-20249-1-2024
11EastGeorgereportG2-1-2024 07:5009:00:0010:00:0011:00:0012:00:0013:00:0014:00:0015:00:00
12NorthJohnreportJ2-1-2024 07:503-1-20244-1-20245-1-20246-1-20247-1-20248-1-20249-1-2024
13NorthJohnreportJ2-1-2024 07:5009:00:0010:00:0011:00:0012:00:0013:00:0014:00:0015:00:00
14SouthPaulreportP2-1-2024 07:503-1-20244-1-20245-1-20246-1-20247-1-20248-1-20249-1-2024
15SouthPaulreportP2-1-2024 07:5009:00:0010:00:0011:00:0012:00:0013:00:0014:00:0015:00:00
16WestRingoreportR2-1-2024 07:503-1-20244-1-20245-1-20246-1-20247-1-20248-1-20249-1-2024
17WestRingoreportR2-1-2024 07:5009:00:0010:00:0011:00:0012:00:0013:00:0014:00:0015:00:00
Sheet1
 
Last edited:
Upvote 0
Thanks again to all (@KRice , @GraH and @JEC ). I was able to get my table to work as you've done. Took a bit as PQ didn't like the formatting I was trying to use until I realized that all of your outputs made all the columns 'text'.

The ultimate goal would of the times is to have them appear as follows;
09:30:00
09 Hours, 30 minutes

I think this can be done, I just have to do some research to figure it out.
 
Upvote 0
My output was any, not text. That way I could use a custom format with a single condition if the number is above 1 then it is a date else it is a timeformat.
 
Upvote 0
GraH said:
My output was any, not text. That way I could use a custom format with a single condition if the number is above 1 then it is a date else it is a timeformat.
Click to expand...
Sorry...that was my bad. I realized after I sent it that it was how you had it set up.

Some days, I feel like excel beats the crap out of me! Can you share your conditional formatting rule? No matter what I try, the true/false statement always shows the '0.####' as being a greater than zero number...I have confirmed that all my values are set to 'any'
 
Upvote 0
Seems XL2BB does not copy the custom format, I thought it did.
I used [>1]d/mm/yyyy u:mm; u:mm
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

S
  • Solved
Add new power query step to existing VBA
Replies
4
Views
300
smozgur
smozgur
G
  • Question Question
Power Query - Change Type Dynamically
2
Replies
12
Views
2K
gelu
G
S
  • Question Question
Table.Expand duplicates some Files when using folder connect? Tried everything I know.
Replies
1
Views
328
GraH
GraH
E
  • Question Question
Dynamic Sheet Name - Power Query
2 3
Replies
23
Views
7K
egoosen3
E
L
  • Solved
Power Query to Determine Consecutive and Non-Consecutive Dates
2 3
Replies
26
Views
1K
legalhustler
L

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top