Forum Discussion

jwb3d's avatar
jwb3d
Frequent Visitor
4 months ago
Solved

Append Column to rows derived from the from two columns

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

fidphaseCountphaseextractDatedeviceNameIndexNew Column
2018007223ABC3/3/2026Device11Device1A
2018007223ABC3/3/2026Device12Device1B
2018007223ABC3/3/2026Device13Device1C
8210472932AC3/3/2026Device1019Device1A
8210472932AC3/3/2026Device1020Device1B
7201836501D3/3/2026Device918Device9D

 

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"

Need Help
  • OwenAuger's avatar
    OwenAuger
    4 months ago

    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?

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    4 months ago

    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?

  • dufoq3's avatar
    dufoq3
    Community Champion
    4 months ago

    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