# Better Power Query Formula to get a list of table columns name ?



## thomsont (Nov 21, 2019)

Currently, I am using the following steps in Power Query to get a list of columns for List.Sum function in a later step.
There is the Table format

Date            Store                    Product                      Discount.A               Discount.A%          Discount.B                Discount.B%         Discount.C         Discount.C%


Discount.A, Discount.B and Discount.C might not appear all the time (This is a sample only, the real report with much more columns)

The current working steps as below:

    Source = SalesReport
    ColumnsList = Table.FromList(Table.ColumnNames(Source), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Discount Columns" = Table.ToList(Table.SelectRows(ColumnsList , each Text.StartsWith([Column1], "Discount.") and not Text.EndsWith([Column1], "%"))),
   TotalDiscountColumns = Table.AddColumn(Source,"Total Discount", each List.Sum(Record.FieldValues(Record.SelectFields(_, #"Discount Columns"))),Currency.Type),

I saw the following function online using List.Difference as

    List.Difference(List.Contains(Table.ColumnNames(Source),{"Discount."}), {"%"})

However, it appears List.Difference not working very well on the % symbol. 

PS: Pivot and UnPivot not working very well as there are numbers of rows and columns in source file.


----------



## sandy666 (Nov 22, 2019)

i got two errors
`A cyclic reference was encountered during evaluation`
`Token comma expected`

I suggest to post a proper M-code and wrap it in CODE tags


----------



## billszysz (Nov 22, 2019)

thomsont said:


> Source = SalesReport
> ColumnsList = Table.FromList(Table.ColumnNames(Source), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
> #"Discount Columns" = Table.ToList(Table.SelectRows(ColumnsList , each Text.StartsWith([Column1], "Discount.") and not Text.EndsWith([Column1], "%"))),
> TotalDiscountColumns = Table.AddColumn(Source,"Total Discount", each List.Sum(Record.FieldValues(Record.SelectFields(_, #"Discount Columns"))),Currency.Type),


 Proposition

```
let
    Source = SalesReport,
    ColumnsToSum = List.Buffer(List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Discount") and not Text.EndsWith(_, "%"))),
    TotalDiscountColumns = Table.AddColumn(Source, "Total Discount", each List.Sum(Record.ToList(Record.SelectFields(_,ColumnsToSum))))
in
    TotalDiscountColumns
```


----------

