Forum Discussion

wiczit's avatar
wiczit
New Member
5 years ago
Solved

Transform.Columns with TextCombine

Hi everybody, hope you're doing great.   In advance, I am quite a neewbie in the advanced use of M language, apologies.   Summary I need your help to concatenate a value within the same column (...
Need Help
Tutorial Requests
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcgRiU6VYnWilJCALiEqKSlPBfGcgJwWIXdPSlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [stringField1 = _t, stringField2 = _t, other = _t]),
    Transformed = List.Accumulate({"stringField1","stringField2"}, Source, (s,c) => Table.TransformColumns(s, {c, each if Text.Length(_)=0 then null else """"&_&""""}))
in
    Transformed

AlB's avatar
AlB
Community Champion
5 years ago

Hi wiczit 

Can you share some sample data (in text-tabular format so that it can be copied) and the expected result?

 

 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

wiczit's avatar
wiczit
New Member
5 years ago

Hi AlB many thanks for your intrest to help me out.

 

 

Given this simple table and this Code that works as the workaround:

 

 

let
    Quelle = Source,
    replaceNull_1 = Table.ReplaceValue(Quelle,"",null,Replacer.ReplaceValue,{"stringField"}),
    replaceNull_2 = Table.ReplaceValue(replaceNull_1,"",null,Replacer.ReplaceValue,{"stringField2"}),
    TextCombined1 = Table.AddColumn(replaceNull_2, "stringField_new1", each if [stringField] = null then null else Text.Combine({"""", [stringField], """"})),
    TextCombined2 = Table.AddColumn(TextCombined1, "stringField_new2", each if [stringField2] = null then null else Text.Combine({"""", [stringField2], """"})),
    SelectedColumns = Table.SelectColumns(TextCombined2,{"Target", "intField", "booleanField", "stringField_new1", "stringField_new2"}),
    RenamedColumns = Table.RenameColumns(SelectedColumns,{{"stringField_new1", "stringField"}, {"stringField_new2", "stringField2"}}),
    ReorderedColumns = Table.ReorderColumns(RenamedColumns,{"Target", "stringField", "stringField2", "intField", "booleanField"})
in
    ReorderedColumns

 

 

 

My attempt to solve this more efficient instead does not work so well.

 

 

let
    Source = Source,
    transform = Table.TransformColumns(Source,each [stringField],Text.Combine("""",[stringField],""""))
in
    transform

 

 

Any advice ?

 

Best
M

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcgRiU6VYnWilJCALiEqKSlPBfGcgJwWIXdPSlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [stringField1 = _t, stringField2 = _t, other = _t]),
    Transformed = List.Accumulate({"stringField1","stringField2"}, Source, (s,c) => Table.TransformColumns(s, {c, each if Text.Length(_)=0 then null else """"&_&""""}))
in
    Transformed

    • wiczit's avatar
      wiczit
      New Member
      5 years ago

      Thanks CNENFRNL that's the result I've expected.

      But to be honest, this is not as straight forward as I've tought it will be.

       

      Apreciating your support and as a learner I would like to understand your solution to have a (modest) chance to transfer this into new areas and to annoy you again (and again...).

       

      let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcgRiU6VYnWilJCALiEqKSlPBfGcgJwWIXdPSlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)),

       I think the first part of the line creates just the table as shown and the fun part starts from here:

       

       let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [stringField1 = _t, stringField2 = _t, other = _t]),

      What does this do? Can you pass me a #tag or a link for further info?

       

      text


      Transformed = List.Accumulate({"stringField1","stringField2"}, Source, (s,c) => Table.TransformColumns(s, {c, each if Text.Length(_)=0 then null else """"&_&""""}))
      in
      Transformed

       List Accumulate is executed in the List {} that contains StringField1 and stringField2.

      the second parameter is the "seed", so seed is the previous step "Source".

      the accumulator is then the third parameter that is built with the function Table.TransformColumns, where conditonally _ is cocantenated with double quotes.

      Here I just don't understand the utilization of the parameters s,c and _?

      Where do they come from? Do you have any links for further info sources on these?
      At least the Table.TransformColumns help page does not tell how to do it.

       

      Thanks again and in advance for any support.

      Best M