Forum Discussion

M001's avatar
M001
Helper I
2 years ago
Solved

Turn column names in M Language defined by variables

Hi there,    I would like to check the data in my latest month column against previous month, and return Y for rows with data in the last two columns not equal   The tricky part is the number of ...
Need Help
  • slorin's avatar
    slorin
    2 years ago

    Hi M001 

     

    if List.IsDistinct(List.LastN(Record.ToList(_),2)) then "N" else "Y"

    Stéphane 

WanderingBI's avatar
WanderingBI
Resolver III
2 years ago

If you hardcode the column names your query will be very hard to maintain in the long run.

Normally it is better to first get your input data into a more "standard" data table layout (columns for field names and rows for values). The reason is that all the functions in Power Query are more aimed at a layout like that. In your case it might also be better to not just to a lot of adjustment steps in Power Query (like transposing the table), but to instead adjust your data source (as an example an Excel file) in the first place. Column names are really not the right place to store transactional data.

 

MonthValue
2023/01/013
2023/02/0110

 

If you really are after months it would also be advisable to to transform your dates to months, or months and years.