Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
oHi,
the subject isn't clear, but let me explain that in an example and it'll be esier:
I've two columns in this way:
Contracts: Scope of Work:
Contracts 1 Inverter L1,L2
Contracts 1 Inverter L3
Contracts 1 Inverter Supply
Contracts 2 Tracker L1,L2
Contracts 2 Tracker L3
Contracts 2 Tracker Supply
I want trasform it in this way
Contracts Scope of Work
Contracts 1 Inverter L1,L2+Inverter L3+Inverter Supply
Contracts 2 Tracker L1,L2+Tracker L3+Tracker Supply
So i want concatenate the information in scope of work in one row for every contracts, is possible?
Thank you all.
Flavio
Solved! Go to Solution.
@Anonymous - You can do with Power Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKylKTC4pVjBU0lHyzCtLLSpJLVLwMdTxMVKK1cGtwBifbHBpQUFOJZoKI6CKECAzG4f5KPLoxiNLwkyPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contracts = _t, #"Scope of Work" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contracts", type text}, {"Scope of Work", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Contracts"}, {{"Grouped", each _, type table [Contracts=nullable text, Scope of Work=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.ToList(Table.SelectColumns([Grouped],"Scope of Work"),Combiner.CombineTextByDelimiter(","))),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "+"), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Grouped"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","""","",Replacer.ReplaceText,{"Custom"})
in
#"Replaced Value"
Proud to be a Super User!
Hi I need this also in new column with Dax measure, please help
@Anonymous - You can do with Power Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKylKTC4pVjBU0lHyzCtLLSpJLVLwMdTxMVKK1cGtwBifbHBpQUFOJZoKI6CKECAzG4f5KPLoxiNLwkyPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contracts = _t, #"Scope of Work" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contracts", type text}, {"Scope of Work", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Contracts"}, {{"Grouped", each _, type table [Contracts=nullable text, Scope of Work=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.ToList(Table.SelectColumns([Grouped],"Scope of Work"),Combiner.CombineTextByDelimiter(","))),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "+"), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Grouped"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","""","",Replacer.ReplaceText,{"Custom"})
in
#"Replaced Value"
Proud to be a Super User!
@Anonymous ,
refer three methods
Concatenate Text- Measure, DAX Table, and Power Query Table: https://youtu.be/xAh3tz1qo24
User | Count |
---|---|
106 | |
85 | |
81 | |
73 | |
71 |
User | Count |
---|---|
111 | |
102 | |
97 | |
74 | |
67 |