Forum Discussion
Append Column to row derived from the from three columns
I would like to include a column that is derived from the deviceName,phase and fid. This would involve appending A-B-C to each of the duplicated rows.
Example
fid phaseCount phase extractDate deviceName deviceNamePhase
201800722 3 ABC 3/3/2026 Device1 Device1_A_201800722
201800722 3 ABC 3/3/2026 Device1 Device1_B_201800722
201800722 3 ABC 3/3/2026 Device1 Device1_C_201800722
821047293 2 AC 3/3/2026 Device10 Device10_821047293
821047293 2 AC 3/3/2026 Device10 Device10_821047293
720183650 1 D 3/3/2026 Device9 Device9_D_720183650
Here is my query
let
Source = Sql.Database("SQLUTLCLP-002\QIRD02,11111", "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
#"Add deviceNamePhase" = Table.AddColumn(
Source,
"deviceNamePhase",
each
let
deviceName = [deviceName]&"_"
in
List.Transform(Text.ToList([phase]), each deviceName & _),
type {text}
),
#"Expand deviceNamePhase" = Table.ExpandListColumn(#"Add deviceNamePhase", "deviceNamePhase"),
#"Add Index" = Table.AddIndexColumn(#"Expand deviceNamePhase", "Index", 1, 1, Int64.Type)
in
#"Add Index"
If your original data looks something like:
Then you can use this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDAwMDcyUtJRcnRyBpLG+sb6RgZGZkCmS2pZZnKqoVKsTrSShZGhgYm5kaUxSCF2dQZgheYgI43NTA1AEtjUWSrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fid = _t, phase = _t, extractDate = _t, deviceName = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{ {"fid", Int64.Type}, {"phase", type text}, {"extractDate", type date}, {"deviceName", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "deviceNamePhase", (x)=> List.Transform( Text.ToList(x[phase]), each x[deviceName] & "_" & _ & "_" & Number.ToText(x[fid]))), #"Expanded deviceNamePhase" = Table.ExpandListColumn(#"Added Custom", "deviceNamePhase") in #"Expanded deviceNamePhase"to produce:
Hopefully this will point in the right direction.
3 Replies
- lbendlin
Super User
I'm not clear what your source data looks like and what your expected outcome is - can you please clarify?
fid phaseCount phase extractDate deviceName 201800722 3 ABC 3-Mar-26 Device1 821047293 2 AC 3-Mar-26 Device10 720183650 1 D 3-Mar-26 Device9 
ronrsnfldIf your original data looks something like:
Then you can use this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDAwMDcyUtJRcnRyBpLG+sb6RgZGZkCmS2pZZnKqoVKsTrSShZGhgYm5kaUxSCF2dQZgheYgI43NTA1AEtjUWSrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fid = _t, phase = _t, extractDate = _t, deviceName = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{ {"fid", Int64.Type}, {"phase", type text}, {"extractDate", type date}, {"deviceName", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "deviceNamePhase", (x)=> List.Transform( Text.ToList(x[phase]), each x[deviceName] & "_" & _ & "_" & Number.ToText(x[fid]))), #"Expanded deviceNamePhase" = Table.ExpandListColumn(#"Added Custom", "deviceNamePhase") in #"Expanded deviceNamePhase"to produce:
Hopefully this will point in the right direction.
