CODEWITHSUNDEEP
excelpowerquery
JohnyL
JohnyL

Reputation: 7132

Columns types are gone after expanding table with Table.ExpandTableColumn in PowerQuery

I was making a query, and once I finished it, I got lots of errors. After some exploration I figured out the cause of errors - after expanding columns from grouping (sum aggregation) all the columns types, assigned on previous steps, have gone completely. So here's example.

Let's say, I have the following table:

IMG1

PowerQuery query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(
                     Source,
                     {
                         {"ID", Int64.Type},
                         {"Date1", type date},
                         {"Sum", Int64.Type}
                     }),
    GroupedRows = Table.Group(
                     ChangedType,
                     {"ID"},
                     {
                         {"SUM_GROUP", each List.Sum([Sum]), type number}, 
                         {"ALL_ROWS", each _, type table}
                     }),
    ExpandedALLROWS = Table.ExpandTableColumn(
                         GroupedRows,
                         "ALL_ROWS",
                         {"ID", "Date1", "Sum"},
                         {"ALL_ROWS.ID", "ALL_ROWS.Sum", "ALL_ROWS.Date1"})
in
    ExpandedALLROWS

On ChangedType step everything is OK:

IMG2

But on ExpandedALLROWS step all types are gone:

IMG3

Is there a way to retain columns types? Or should I change types in the end of a query?

Upvotes: 2

Views: 1338

Answers (1)

Gangula
Gangula

Reputation: 7284

There's actually no solution for this as of now. There's a similar question posted in Power BI Community. You can check it out to see how you can go about it.

There's an idea posted in Power BI ideas website. You can vote it to get it implemented soon.

There also a blogpost that might be helpful depending on how you can customize the code to your need.

Upvotes: 3

Related Questions