Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
M001
Helper I
Helper I

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 columns are adding every month so the column name for the latest month and the month prior are changing every month, e.g Latest will be 1/6/2024 when in June and it will change to 1/7/2024 when in July

 

Fabric_Q1.png

 

My approach was:

 

1. Identify the latest month and the prior month >> turn them into variable as LatestMonth and PriorMonth. I have to make the latest month and prior month as variable because the latest month is changing every month

 

 

LatestMonth = 1/6/2024
PriorMonth = 1/5/2024

 

 

2. Replace [#"1/6/2024"] to LatestMonth and [#"1/5/2024"] to PriorMonth in the following code

 

 

= Table.AddColumn(FilteredStep, "RecentUpdate", each if [#"1/6/2024"] = [#"1/5/2024"] then "N" else "Y")

 

 

 

 

 

= Table.AddColumn(FilteredStep, "RecentUpdate", each if LatestMonth = PriorMonth then "N" else "Y")

 

 

 

Unfortunately, it doesnt work, and hope someone could enlighten me. Thank you!!

 

 

Regards,

1 ACCEPTED SOLUTION
slorin
Super User
Super User

Hi @M001 

 

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

Stéphane 

View solution in original post

6 REPLIES 6
dufoq3
Super User
Super User

Hi @M001, check this:

 

Result

dufoq3_0-1718805109453.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiMgNgZiEyB2dFSK1YlWcoJKwaRB2NUVLOWMJAyjnZzAUi5IhhlD2c7OYClXJDtg2MVFKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"1/3/2024" = _t, #"1/4/2024" = _t, #"1/5/2024" = _t, #"1/6/2024" = _t, Column2 = _t]),
    Ad_RecentUpdate = Table.AddColumn(Source, "RecentUpdate", each 
        [ a = List.LastN(List.Sort(List.Select(Table.ColumnNames(Source), (x)=> (try Date.From(x) otherwise null) is date)), 2),
          b = if Record.Field(_, a{0}) <> Record.Field(_, a{1}) then "Y" else "N"
        ][b], type text)
in
    Ad_RecentUpdate

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

m_dekorte
Super User
Super User

Hi @M001,

 

Copy this M code into a new blank query, replacing its content with:

(t as table) as table =>
let
    cols = Table.ColumnNames( t ),
    dates = List.Transform( cols, each 
        try Date.FromText(_, [Culture="en-GB"]) otherwise null 
    ),
    names = Table.MaxN( 
        Table.FromColumns(
            { cols, dates }, 
            type table[ Fieldname=text, Value= any]
        ), "Value", 2 
    )[Fieldname],
    addCol = Table.AddColumn( t, "Recent update", each
        if Record.Field(_, names{0}) = Record.Field(_, names{1}) 
        then "N" 
        else "Y", type text
    )
in
    addCol

Rename this new query to: fxRecentUpdate

Now you can invoke it on your table by selecting it from the drop down.

m_dekorte_0-1718803370892.png

Note. This does require at least two "date field" column names to be present else it will return an error.

 

I hope this is helpful

Hi @m_dekorte Melissa, and thank you for helping to solve this practical problem with PowerQuery/M.

 

This problem would be a great addition to your LinkedIn. Today Menu series.

slorin
Super User
Super User

Hi @M001 

 

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

Stéphane 

Thank you, Stephane and everyone. I think this is the simplest approach among all the helpful feedback, just that the N and Y were placed wrongly, and we need to make sure the two columns must be at the last two position

WanderingBI
Resolver III
Resolver III

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors