Forum Discussion

bbuchmeyer's avatar
bbuchmeyer
Frequent Visitor
7 years ago
Solved

Transpose and Remove Columns Across Multiple Tables

Hi there! Lets say I have a data set, in which I have grouped together by Project Name.                   Within each project, I have a table containing several dates I want to com...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI bbuchmeyer,

     

    >>Say delete columns, transpose, then like 7 more steps. I have tried to use open parenthese and the "&" symbol to do several steps in one but no luck.

    It is possible, you need to use previous steps formula as source of next step and use '_' to replace datasource of first step.

     

    Sample: below bold part need to be apply to all grouped tables.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7LDcAgDAPQXXJGyg9SdkHsv0ZJW1r3Fj2B7TFIqZCysokeeYrQLJtbcl+nATeOzQ7c75AOIfZmS+RHYGPflQEM2QYM2fVh/wbWfN2QLTlgoP8rMQQqr4HzBA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Value", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"ID"}, {{"Contents", each _, type table}}),
    #"1" = #"Grouped Rows"{[ID=1]}[Contents],
    #"Removed Columns" = Table.RemoveColumns(#"1",{"ID"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1)
in
    #"Added Index"

     

    Transformed query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7LDcAgDAPQXXJGyg9SdkHsv0ZJW1r3Fj2B7TFIqZCysokeeYrQLJtbcl+nATeOzQ7c75AOIfZmS+RHYGPflQEM2QYM2fVh/wbWfN2QLTlgoP8rMQQqr4HzBA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Value", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"ID"}, {{"Contents", each Table.AddIndexColumn(Table.RemoveColumns(_,{"ID"}), "Index", 1, 1), type table}})

in
    #"Grouped Rows"

    Hope above helps.

     

    Regards,
    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

HI bbuchmeyer,

 

>> I can do this on an individual table no problem, but if I wanted to do this to 8 tables at once, and only bring the computational column to the group view, it becomes troublesome.

For your requirement, you need to customize grouping function to nested with other functions.(bold part)

#"Grouped Rows" = Table.Group(#"Changed Type", {"Work Item Id"}, {{"Contents", each  _, type table}})

 

Sample: add index for all group tables at same time.

 

BTW, if you confuse to coding formula, you can share some sample data for test.

 

Regards,

Xiaoxin Sheng

bbuchmeyer's avatar
bbuchmeyer
Frequent Visitor
7 years ago

Thanks! Completely makes sense, but what if it is multiple applications at once?

 

Say delete columns, transpose, then like 7 more steps. I have tried to use open parenthese and the "&" symbol to do several steps in one but no luck.

 

Ideas?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI bbuchmeyer,

     

    >>Say delete columns, transpose, then like 7 more steps. I have tried to use open parenthese and the "&" symbol to do several steps in one but no luck.

    It is possible, you need to use previous steps formula as source of next step and use '_' to replace datasource of first step.

     

    Sample: below bold part need to be apply to all grouped tables.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7LDcAgDAPQXXJGyg9SdkHsv0ZJW1r3Fj2B7TFIqZCysokeeYrQLJtbcl+nATeOzQ7c75AOIfZmS+RHYGPflQEM2QYM2fVh/wbWfN2QLTlgoP8rMQQqr4HzBA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Value", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"ID"}, {{"Contents", each _, type table}}),
    #"1" = #"Grouped Rows"{[ID=1]}[Contents],
    #"Removed Columns" = Table.RemoveColumns(#"1",{"ID"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1)
in
    #"Added Index"

     

    Transformed query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7LDcAgDAPQXXJGyg9SdkHsv0ZJW1r3Fj2B7TFIqZCysokeeYrQLJtbcl+nATeOzQ7c75AOIfZmS+RHYGPflQEM2QYM2fVh/wbWfN2QLTlgoP8rMQQqr4HzBA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Value", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"ID"}, {{"Contents", each Table.AddIndexColumn(Table.RemoveColumns(_,{"ID"}), "Index", 1, 1), type table}})

in
    #"Grouped Rows"

    Hope above helps.

     

    Regards,
    Xiaoxin Sheng