Append Column to rows derived from the from two columns

[jwb3d]

I would like to include a column that is derived from the deviceName and phase. This would involve appending A-B-C to each of the duplicated rows.

Example

fid	phaseCount	phase	extractDate	deviceName	Index	New Column
201800722	3	ABC	3/3/2026	Device1	1	Device1A
201800722	3	ABC	3/3/2026	Device1	2	Device1B
201800722	3	ABC	3/3/2026	Device1	3	Device1C
821047293	2	AC	3/3/2026	Device10	19	Device1A
821047293	2	AC	3/3/2026	Device10	20	Device1B
720183650	1	D	3/3/2026	Device9	18	Device9D

Here is my query

let
Source = Sql.Database("SQLUTLCLP-002\PROD02,51818", "DMS", [Query="select *#(lf) #(lf)#(lf)from dms.eccl.gisDeviceChanges ss#(lf)where#(lf) #(lf)ss.phaseCount > 0"]),
ColNames = Table.ColumnNames(Source),

// Nest each row as a repeated table
AddedRepeated = Table.AddColumn(
Source,
"RepeatedRows",
each Table.Repeat( Table.FromRecords({ _ }), [phaseCount] )
),

// the id is causing error so drop originals BEFORE expanding to avoid duplicate field error
OnlyNested = Table.SelectColumns(AddedRepeated, {"RepeatedRows"}),

Expanded = Table.ExpandTableColumn(
OnlyNested, "RepeatedRows", ColNames
),
#"Added Index" = Table.AddIndexColumn(Expanded, "Index", 1, 1, Int64.Type)

in
#"Added Index"

[dufoq3]

Hi @jwb3d,

Before:

After:

Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDAwMDcyUtJRMgZiRydnEEvfWN/IwMgMyHRJLctMTjVUitWJVrIwMjQwMTeyBCkEaXDErtYArNgcZLSxmakBUMIQJIlNraVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fid = _t, phaseCount = _t, phase = _t, extractDate = _t, deviceName = _t]),
    DeviceNameList = Table.ReplaceValue(Source,
        each List.Transform(Text.ToList([phase]), (x)=> [deviceName] & x),
        null,
        (x,y,z)=> y,
        {"deviceName"} ),
    ExpandedDeviceName = Table.ExpandListColumn(DeviceNameList, "deviceName")
in
    ExpandedDeviceName

[OwenAuger · Accepted solution]

Hi @jwb3d 

Will the phase column always contain the list of characters to append to deviceName concatenated as a string?

If so, I would consider a query similar to the example below (Source step hard-coded to illustrate):

let
  Source = #table(
    type table [
      fid         = Int64.Type,
      phaseCount  = Int64.Type,
      phase       = text,
      extractDate = date,
      deviceName  = text
    ],
    {
      {201800722, 3, "ABC", #date(2026, 3, 3), "Device1"},
      {821047293, 2, "AC", #date(2026, 3, 3), "Device10"},
      {720183650, 1, "D", #date(2026, 3, 3), "Device9"}
    }
  ),
  #"Add deviceNamePhase" = Table.AddColumn(
    Source,
    "deviceNamePhase",
    each List.Transform(Text.ToList([phase]), (phaseChar) => [deviceName] & phaseChar),
    type {text}
  ),
  #"Expand deviceNamePhase" = Table.ExpandListColumn(#"Add deviceNamePhase", "deviceNamePhase"),
  #"Add Index" = Table.AddIndexColumn(#"Expand deviceNamePhase", "Index", 1, 1, Int64.Type)
in
  #"Add Index"

Would something like this work for you?