One Step Transfomation

[yevhen]

let
  Source = Table.CombineColumns(
  Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="CombineColumns"]}[Content], {{"A", type text},{"B", type text},{"C", type text}}), {"A","B","C"}, Combiner.CombineTextByDelimiter("/", QuoteStyle.None), "ABC")
in
    Source

Hi everyone. Need help on how to figure out to make column names not hard coded but dynamic, so when new columns will be added the query would work.

 

[AlienSx]

@yevhen , do you really need "one step" solution?

let
    one_step = ((source) => ((columns) => Table.CombineColumns(
        source, 
        columns, 
        (x) => Text.Combine(List.Transform(x, Text.From), "/"), Text.Combine(columns)
    ))
    (Table.ColumnNames(source)))(Excel.CurrentWorkbook(){[Name="CombineColumns"]}[Content])
in
    one_step

 

[AlienSx]

these 2 options look simpler

let
    one_step = ((source) => Table.FromList(
        Table.ToList(source, (x) => {Text.Combine(List.Transform(x, Text.From), "/")}),
        (x) => x, 
        {Text.Combine(Table.ColumnNames(source))}
    )
    )(Excel.CurrentWorkbook(){[Name="CombineColumns"]}[Content])
in
    one_step

let
    one_step = ((source) => #table(
        {Text.Combine(Table.ColumnNames(source))}, 
        Table.ToList(source, (x) => {Text.Combine(List.Transform(x, Text.From), "/")})
    )
    )(Excel.CurrentWorkbook(){[Name="CombineColumns"]}[Content])
in
    one_step

 

[yevhen]

these 2 options look simpler

let
    one_step = ((source) => Table.FromList(
        Table.ToList(source, (x) => {Text.Combine(List.Transform(x, Text.From), "/")}),
        (x) => x,
        {Text.Combine(Table.ColumnNames(source))}
    )
    )(Excel.CurrentWorkbook(){[Name="CombineColumns"]}[Content])
in
    one_step

let
    one_step = ((source) => #table(
        {Text.Combine(Table.ColumnNames(source))},
        Table.ToList(source, (x) => {Text.Combine(List.Transform(x, Text.From), "/")})
    )
    )(Excel.CurrentWorkbook(){[Name="CombineColumns"]}[Content])
in
    one_step

Thanks a lot. It works great. Just making my first steps on understanding the M language, and I am amazed with its flexibility. But as for now, it functions as a bit of an advanced topic to me, and I am wondering if it could be implemented with the help of lists?

 

[AlienSx]

@yevhen, M is functional language. I have nothing to add to this statement.

 

[Automatrix]

Here's a solution that's mostly UI-based, with two hand-written custom columns in step 5 and 6. They make a list of specific columns from the tables generated with the Group By action.

let
    Source = Excel.CurrentWorkbook(){[Name="your_data"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Index"}, {{"All Rows", each _, type table [Index=number, Attribute=text, Value=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Row headers", each Table.Column([All Rows], "Attribute")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Row values", each Table.Column([All Rows], "Value")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Row headers", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Row values", each Text.Combine(List.Transform(_, Text.From), "/"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values1",{"All Rows"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Row headers"]), "Row headers", "Row values"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"