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 fid phaseCount phase extractDate de...
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?

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