Combining several line items into one column in PowerQuery

[chloebelle]

Hopefully I am phrasing this coherently. Basically, if an employee clocks out for their break and has several time durations during one shift, the output from my job's scheduling system will be split, as shown in the table below (only difference is that everything is actually in one row - the shift duration times are split by a paragraph break, not in a separate row). When I try to load the data into Power BI, it looks like the photo below. I currently solve this in Excel using this function =IFERROR(TEXTSPLIT(X,CHAR(10))," "), which splits each line item into their own column, but the goal is to have this process entirely automated in PowerBI.

If anyone has any idea on how I could transform the data in PowerQuery that would be super helpful, thanks!

LEGAL NAME	DATE	POSITIONS	SHIFT DURATION	SCH. SHIFT DURATION
John Smith	13-Jun-23	Accounting	5.5	8
			0.5
John Smith	14-Jun-23	Accounting	9.25	8
			1.43
John Smith	15-Jun-23	Accounting	2.67	8
			1
			3.03
John Smith	16-Jun-23	Accounting	4.77	8
			1.5
			0.25

In case it's helpful, this is what it looks like in Excel. Everything after SCH. SHIFT DURATION is a function and roughly what the goal for PowerBI is. Payroll would be ACTUAL * (employee's wage) using xlookup but I changed their name for anonymity. 

[jgeddes]

If you need to keep the columns that indicate the individual shift durations that make up the actual sum you can use the following code.

let
Source = 
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUTI01vUqzdM1MgayHZOT80vzSjLz0oEcUz1TIGmhFKsTrQRkQJABWBAshmqKCQ5TLPWMEMYoAFkwbKhnYgxmYpplisMsIz0zc+xmwQ1CFjXWM8BlgxkOG0z0zHHYAPY3ph0GEO8BJWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"LEGAL NAME" = _t, DATE = _t, POSITIONS = _t, #"SHIFT DURATION" = _t, #"SCH. SHIFT DURATION" = _t]),
changeDataTypes = 
Table.TransformColumnTypes(Source,{{"LEGAL NAME", type text}, {"DATE", type date}, {"POSITIONS", type text}, {"SHIFT DURATION", type number}, {"SCH. SHIFT DURATION", Int64.Type}}),
replaceNulls = 
//replace blank spaces with null to allow fill down
Table.ReplaceValue(changeDataTypes,"",null,Replacer.ReplaceValue,{"LEGAL NAME", "POSITIONS"}),
replaceNulls1 = 
Table.ReplaceValue(replaceNulls," ",null,Replacer.ReplaceValue,{"LEGAL NAME", "POSITIONS"}),
fillDownColumns = 
//fill down to replace blank spaces and allow grouping
Table.FillDown(replaceNulls1,{"LEGAL NAME", "DATE", "POSITIONS", "SCH. SHIFT DURATION"}),
//group by all columns except SHIFT DURATION, choosing all rows (no aggregation)
groupRows = 
Table.Group(fillDownColumns, {"LEGAL NAME", "DATE", "POSITIONS", "SCH. SHIFT DURATION"}, {{"innerTable", each _, type table [LEGAL NAME=nullable text, DATE=nullable date, POSITIONS=nullable text, SHIFT DURATION=nullable number, SCH. SHIFT DURATION=nullable number]}}),
//select only the SHIFT DURATION column in the inner tables 
addGroupedTable = 
Table.TransformColumns(groupRows, {"innerTable", each Table.SelectColumns(_, "SHIFT DURATION")}),
//add column that sums SHIFT DURATION to get actual time worked
addActualColumn = 
Table.AddColumn(addGroupedTable, "ACTUAL", each List.Sum([innerTable][SHIFT DURATION]), type number),
//transpose the inner tables (rows to columns)
transposeInnerTable = 
Table.TransformColumns(addActualColumn, {"innerTable", each Table.Transpose(_)}),
//create a column of lists (needed to build the inner table expansion)
addListCount = 
Table.AddColumn(transposeInnerTable, "listCount", each Table.ColumnNames([innerTable])),
//get distinct list of column names (used in expansion and renaming of columns)
getDistinctList = 
List.Distinct(List.Combine(addListCount[listCount])),
//convert the list to a table in order to manipulate the list values
convertListToTable = 
Table.FromList(getDistinctList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
//replace the 'Column' value with 'SHIFT DURATION' (for renaming of expanded columns)
replaceColumnValue = 
Table.ReplaceValue(convertListToTable,"Column","SHIFT DURATION",Replacer.ReplaceText,{"Column1"}),
//convert the table back into a list
convertToList = 
replaceColumnValue[Column1],
//create a list of lists for renaming of expanded columns
createRenamingList = 
List.Zip({getDistinctList, convertToList}),
//expand the inner table using the created list (getDistinctList) to expand all possible columns
expandInnerTable = 
Table.ExpandTableColumn(addListCount, "innerTable", getDistinctList, getDistinctList),
//rename the columns using the created list of lists (createRenamingList)
renameColumns = 
Table.RenameColumns(expandInnerTable,createRenamingList),
//remove un-needed column
removeColumn = 
Table.RemoveColumns(renameColumns,{"listCount"})
in
removeColumn   

[wdx223_Daniel · Accepted solution]

1 Fill down the columns of name, date, positions

2 Transform the data type of Shift duration to number

3 group the data by the columns of name, date, positions, and sum the shift duration as Actual.

[chloebelle]

Wowowow, easy!!! Thank you so much!