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 (if its not null) or with other words to wrap a string value with double quotes without the creation of addtional- and removal of columns.

 

 

Scenario

- limited to use MS Excel 2013 + PQ Addon

Objective is to create and manage Testdata for an API Test with MS Excel.

PQ is used for further processing of Testdata and providing it finally in JSON format.

Postman (newman in particular) uses the JSON for test execution.

 

Workaround

Create an additional column ( for each column that requires transformation)

 

[section from advancedEditor]

addedColumn = Table.AddColumn(Source, "columnNameNew", each Text.Combine({"""", [columnName], """"}), type text)

Question

I am asking myself if there's a more efficient way like the combination of Table.TransformColumns with Text.Combine.

Unfortunately the following approach does not work.

Table.TransformColumns(A,{{"columnName",Text.Combine({"""", [columnName], """"}), type text}})

 

Appreciate any help links etc. espcially if this refers to better basic understanding of M lang.

 

 

Cheers, Martin

 

 

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

5 Replies

  • 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