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

thomsont

New Member
Joined
Nov 24, 2014
Messages
1
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,231
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

Active Member
Joined
Feb 26, 2014
Messages
344
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
Code:
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
 

Forum statistics

Threads
1,077,896
Messages
5,337,057
Members
399,120
Latest member
Sravankumar

Some videos you may like

This Week's Hot Topics

Top