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

[thomsont]

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]

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]

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