Transform.Columns with TextCombine

[wiczit]

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

[AlB]

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]

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 · Accepted solution]

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]

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

[CNENFRNL]

Hi, there. So glad my proposal helps. As to those details, it requires some general understanding of the M language. I recommend this series of blogs as a primer,

https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi...

in fact, thanks to this series, I got familiar with this fanscinating language.