Transpose and Remove Columns Across Multiple Tables

[bbuchmeyer]

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 compare against each other to measure cycle time.

My goal is to delete columns, "project name, BU", then transpose the data, then lastly add computational columns. 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.

[v-shex-msft]

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]

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?

[v-shex-msft · Accepted solution]

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