Power Query - Concatenate field with field above row by row

[cottrera]

Hi Community , I have the following table.   The table is in the following order

1-Job#

2- Start Date

3-Input time as time type

In power query I would like to always start with the second row of each new 'Job#' and concatenate the the column called 'analysis' starting from the fist row of each job number and concatenate the 'analysis' of the next row down. This needs to be repeated  until the rows stop for each 'Job#'. 

I have added an example of the output I would expect on the end column .
Thank you Richard

Date Logged	Job#	AgeOfJobInWorkingDays	audit action	StartDate	EndDate	Input time as time type	WhoChangedIt	Analysis Column I	TotalDurationInStatus	**Power Query**
02/01/2023	8241424		JOB_CREATE	02/01/2023	02/01/2023	11:07:30	John Smith	Awaiting	0
02/01/2023	8241424		JOB_UPDATE	02/01/2023	04/01/2023	11:08:11	John Smith	Awaiting	2	Awaiting-Awaiting
02/01/2023	8241424		JOB_UPDATE	04/01/2023	09/01/2023	12:15:22	Avril Jones	Awaiting	5	Awaiting-Awaiting
02/01/2023	8241424		JOB_UPDATE	09/01/2023	09/01/2023	11:39:49	Avril Jones	Awaiting	0	Awaiting-Awaiting
02/01/2023	8241424		JOB_UPDATE	09/01/2023	09/01/2023	11:40:00	Avril Jones	Awaiting	0	Awaiting-Awaiting
02/01/2023	8241424		APPT_SCHD	09/01/2023	03/02/2023	11:40:48	Avril Jones	Scheduled	25	Awaiting-Scheduled
02/01/2023	8241424		JOB_UPDATE	03/02/2023	03/02/2023	17:04:03	BACKUP	Scheduled	0	Scheduled-Scheduled
02/01/2023	8241432		JOB_CREATE	02/01/2023	02/01/2023	11:15:31	John Smith	Awaiting	0
02/01/2023	8241432		JOB_UPDATE	02/01/2023	04/01/2023	11:15:50	John Smith	Awaiting	2	Awaiting-Awaiting
02/01/2023	8241432		JOB_UPDATE	04/01/2023	04/01/2023	12:01:47	Avril Jones	Awaiting	0	Awaiting-Awaiting
02/01/2023	8241432		JOB_UPDATE	04/01/2023	04/01/2023	12:05:07	Avril Jones	Awaiting	0	Awaiting-Awaiting
02/01/2023	8241432		APPT_SCHD	04/01/2023	23/01/2023	12:05:41	Avril Jones	Scheduled	19	Awaiting-Scheduled
03/01/2023	8242000		JOB_CREATE	03/01/2023	03/01/2023	09:54:35	Tony Banham	Awaiting	0
03/01/2023	8242000		JOB_UPDATE	03/01/2023	11/01/2023	09:54:50	Tony Banham	Awaiting	8	Awaiting-Awaiting
03/01/2023	8242000		APPT_SCHD	11/01/2023	18/01/2023	11:24:07	Christine Foskett	Scheduled	7	Awaiting-Scheduled
03/01/2023	8242000		APPT_CANCL	18/01/2023	18/01/2023	09:55:53	Shaine Masterton	Awaiting	0	Scheduled-Awaiting
03/01/2023	8242000		APPT_SCHD	18/01/2023	01/02/2023	09:56:05	Shaine Masterton	Scheduled	14	Awaiting-Scheduled
03/01/2023	8242000		APPT_CANCL	01/02/2023	03/02/2023	11:59:18	BACKUP	Awaiting	2	Scheduled-Awaiting
03/01/2023	8242000		JOB_UPDATE	03/02/2023	16/02/2023	10:27:33	PRATCHSX	Awaiting	13	Awaiting-Awaiting
03/01/2023	8242000		APPT_SCHD	16/02/2023	15/03/2023	11:20:14	Christine Foskett	Scheduled	27	Awaiting-Scheduled
03/01/2023	8242000		APPT_CANCL	15/03/2023	21/03/2023	14:49:05	BACKUP	Awaiting	6	Scheduled-Awaiting
03/01/2023	8242000		JOB_UPDATE	21/03/2023	29/03/2023	17:00:26	Christine Foskett	Awaiting	8	Awaiting-Awaiting
03/01/2023	8242000		APPT_SCHD	29/03/2023	12/04/2023	14:55:33	Christine Foskett	Scheduled	14	Awaiting-Scheduled
03/01/2023	8242000		JOB_COMPL	12/04/2023	28/04/2023	16:22:48	BACKUP	Complete	16	Scheduled-Complete
03/01/2023	8242000		JOB_UPDATE	28/04/2023	28/04/2023	14:43:41	Christine Foskett	Complete	0	Complete-Complete
03/01/2023	8242323		JOB_CREATE	03/01/2023	03/01/2023	10:27:20	Jazaib Sakender	Awaiting	0
03/01/2023	8242323		APPT_SCHD	03/01/2023	03/01/2023	10:27:24	Jazaib Sakender	Scheduled	0	Awaiting-Scheduled
03/01/2023	8242323		JOB_UPDATE	03/01/2023	24/01/2023	10:27:54	Jazaib Sakender	Scheduled	21	Scheduled-Scheduled
03/01/2023	8242802		JOB_CREATE	03/01/2023	03/01/2023	11:20:39	HEWITTNX	Awaiting	0	Scheduled-Awaiting
03/01/2023	8242802		APPT_SCHD	03/01/2023	03/01/2023	11:21:40	HEWITTNX	Scheduled	0	Awaiting-Scheduled
03/01/2023	8242802		JOB_UPDATE	03/01/2023	03/01/2023	11:22:00	HEWITTNX	Scheduled	0	Scheduled-Scheduled
03/01/2023	8242802		APPT_CANCL	03/01/2023	03/01/2023	15:12:46	Shaine Masterton	Awaiting	0	Scheduled-Awaiting
03/01/2023	8242802		APPT_SCHD	03/01/2023	05/01/2023	15:13:06	Shaine Masterton	Scheduled	2	Awaiting-Scheduled

[cottrera]

Amazing thank you 😀 works perfectly 

[cottrera]

Hi M_dekote for you fast response. Looks like the m-code solves my problem.  However I made a slight error on the sample table I sent. There are some additional columns

Due_Overdue
Current Job Status
Trade
UniqueReference
UPRN
Work Stream
Agent

And I also have the following transformation in my existing code.
et
Source = Sql.Database("xxxx", "xxxx"),
Repairs_JobStatusChangesFromAuditTable = Source{[Schema="Repairs",Item="JobStatusChangesFromAuditTable"]}[Data],
#"Removed Columns" = Table.RemoveColumns(Repairs_JobStatusChangesFromAuditTable,{"Job Status", "Input User"}),
#"Merged Queries with Repairs" = Table.NestedJoin(#"Removed Columns", {"Repairs Request Ref"}, Repairs, {"Repairs Request Ref"}, "Repairs", JoinKind.LeftOuter),
#"Expanded Repairs" = Table.ExpandTableColumn(#"Merged Queries with Repairs", "Repairs", {"AgeOfJobBuckets", "AgeOfJobInWorkingDays", "Date Logged", "Due_Overdue", "Job Status Description", "Trade", "UniqueReference", "Unit Reference", "Work Stream"}, {"AgeOfJobBuckets", "AgeOfJobInWorkingDays", "Date Logged", "Due_Overdue", "Job Status Description", "Trade", "UniqueReference", "Unit Reference", "Work Stream"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Repairs",{{"Job Status Description", "Current Job Status"}}),
#"Merged Queries with Agents" = Table.NestedJoin(#"Renamed Columns", {"UniqueReference"}, Agents, {"Unique"}, "Agents", JoinKind.LeftOuter),
#"Expanded Agents" = Table.ExpandTableColumn(#"Merged Queries with Agents", "Agents", {"Agent"}, {"Agent"}),
// By Reactive & Routine
#"Filtered Rows" = Table.SelectRows(#"Expanded Agents", each ([Work Stream] = "REACTIVE" or [Work Stream] = "REACTIVE_A" or [Work Stream] = "REACTIVE_B" or [Work Stream] = "ROUTINE_EXTERNAL" or [Work Stream] = "ROUTINE_INTERNAL" or [Work Stream] = "SMALLWORKS")),
#"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"Repairs Request Ref", "Job#"}, {"Unit Reference", "UPRN"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1","","N/A",Replacer.ReplaceValue,{"Due_Overdue"})
in
#"Replaced Value"

How would you suggest I both add the addtional columns to your code and also add your code after my code (mine obviously is using the UI)
Thanks Again
Richard

[m_dekorte · Accepted solution]

Hi @cottrera 

Give this a go. Open the Advanced Editor select the last 2 lines of code, that's this part

in
#"Replaced Value"

Paste this in its place, it's important that the column "Analysis" from your sample corresponds to a column with the same name in your production data. If not, you need to update that name in the code below.

    GroupRows = Table.Group(#"Replaced Value", {"Job#"}, {{"t", each Table.AddColumn( Table.AddIndexColumn( _, "i", 0, 1), "Result", (x)=> if x[i]-1 <0 then "" else Text.Combine( List.Range([Analysis], x[i]-1, 2), "-"))}}),
    t = Table.Combine( GroupRows[t] )
in
    t

I hope this is helpful

[m_dekorte]

Hi @cottrera,

Give this a go.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZdbb9owFMe/SsReO+FbIPEbDUylazvUULVSVVVuiZqokCAw27pPvxNnAjv3FPZQYZPg3zl/n1sfH3tjIQPrKnl7Cxa9s95l8vIFPsRuEUlLvMooiWHrS7GR6YuwnsSLf6tpvN5JS0arwBLb7FN+rNMn92HihSKGI6cStqNYLD+20RaW80SK5Xi3EenJ0xgOlrv0++D3OniVYMLT2WMPkT7CfYIIhScOYZgRltr24/zZu52M5hPYGO8YG4w5GnKKlDdhbPmrSIapFb9EJKP4LX0f/qwm1N1sXIZiOZTDMa5BEW37df99a7ROQ66OJhzbnKjjf26ipXWZxMHWZNvHsd1KNubU5cytY6P/x2aII3RS9mg2mz/73sW4gKZ9+ImBZk4B7b+GwWK3VAlEDNEPT1p7rhNN/JAjxlG6PB953+9mOTLS941kSjplFMQarQvzuozao9plFKDsuuTtklElaFaJJhzBDQ9PFVld2TaUrZOyjajWaYQW0AzXRjV2a6KamnSCVHaasUVzSaUlN7cZp7bqDfGHdS7iUKwqg6uaZWSQFk8FlgquSpZTrXI5W1fZoGHHjGvCsgv2wk20hVMD61uyfQ+kzGk97Cy1MsEb3XhXBayTcx9yK136oUgNuBZbGWyk6vE5vQ+VpLMIBhPr5QwMGEC0lRtgxBs7TgQTmyvitsuxo1fRXHHp5np19cYDfYM4gakkXc5uR3Pvwn8wyZgeFXkGy+7DT7TIQ1wJ2hR55NjQ07kE60YwGBeymy9TfXCc6gaKuDoXeiYIP6hw/lR5bzKhGjPNccg5de1N6n8i5lWN/XE9u8pjiaPbMIA5MZtb9uJ7yWq9DNQYj031909aq+9UguHWadZaypzXbEDatsECqlYtu0uWdUTZLP6I6MXyxXsQL4JN6w6T8Yxe2oRjpbj8rNb2qg8Ol7c4wgoW2M0WENyrGRfzNjioOC5Wq6AqDk1HhovJ/XQ+v3koqt023TNyS/kBnM7oJvizuh98Lte9gCbZvyU16I56my2tmm1zGOKYSuOT9fUm3e2cAZSjCgOMqKvQ/+kv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t]),
    PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    GroupRows = Table.Group(PromoteHeaders, {"Job#"}, {{"t", each Table.AddColumn( Table.AddIndexColumn( _, "i", 0, 1), "Result", (x)=> if x[i]-1 <0 then "" else Text.Combine( List.Range([Analysis], x[i]-1, 2), "-")), type table [Date Logged=nullable text, #"Job#"=nullable text, audit action=nullable text, StartDate=nullable text, EndDate=nullable text, Input time as time type=nullable text, WhoChangedIt=nullable text, Analysis=nullable text, TotalDurationInStatus=nullable text, expected=nullable text, Result=text]}}),
    ExpandRows = Table.ExpandTableColumn(GroupRows, "t", {"Date Logged", "Job#", "audit action", "StartDate", "EndDate", "Input time as time type", "WhoChangedIt", "Analysis", "TotalDurationInStatus", "expected", "Result"}, {"Date Logged", "Job#.1", "audit action", "StartDate", "EndDate", "Input time as time type", "WhoChangedIt", "Analysis", "TotalDurationInStatus", "expected", "Result"})
in
    ExpandRows

- group your data, select "all rows"

- add an index from 0 to the nested table

- use it to obtain a range of 2 values and combine those strings

with this result

I hope this is helpful