Reply
JollyRoger01
Helper III
Helper III
Partially syndicated - Outbound

How to delete the last column of data without referencing column names?

I have data that includes a total column at the end that summed across the rows. I want to delete this column, but I can't click on it and delete it as the column name may change when the data source is updated, causing errors. So I am looking for a function of some type that deletes the last column and won't cause any errors if all the column names in the source are changed.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VVFLEoUwCLuLaxcC/dizON7/Go+kTZ23MKMCCQnPc5RxnIdFgjdAPqUkBMA63vBcCTf+3KvJSzve85kD/LnBWDdAFbnxk1R1f9ZOCqqwFZqFjEPqFDQWMFOxCBYteIlkJAOaunhZIh/XdsBY+3O+yp82QDW+IEz10KhBMmy7qYvF0w0I6J1hoVyafLjLhw+1cJ+4t1CKMEgOMYXQEXZoE7pmbPwVIv0zhqqsoq9AuHO4DA3pk2FGTWvXvGYoHp606aJzIypfsv+dg5tH9rzvDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"20201218" = _t, #"20201221" = _t, #"20201222" = _t, #"20201223" = _t, #"20210104" = _t, #"20210105" = _t, #"20210106" = _t, #"20210107" = _t, #"20210108" = _t, #"20210110" = _t, #"20210111" = _t, #"20210112" = _t, #"20210113" = _t])
in 
    Source
4 REPLIES 4
avatar user
Anonymous
Not applicable

Syndicated - Outbound

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VVFLEoUwCLuLaxcC/dizON7/Go+kTZ23MKMCCQnPc5RxnIdFgjdAPqUkBMA63vBcCTf+3KvJSzve85kD/LnBWDdAFbnxk1R1f9ZOCqqwFZqFjEPqFDQWMFOxCBYteIlkJAOaunhZIh/XdsBY+3O+yp82QDW+IEz10KhBMmy7qYvF0w0I6J1hoVyafLjLhw+1cJ+4t1CKMEgOMYXQEXZoE7pmbPwVIv0zhqqsoq9AuHO4DA3pk2FGTWvXvGYoHp606aJzIypfsv+dg5tH9rzvDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"20201218" = _t, #"20201221" = _t, #"20201222" = _t, #"20201223" = _t, #"20210104" = _t, #"20210105" = _t, #"20210106" = _t, #"20210107" = _t, #"20210108" = _t, #"20210110" = _t, #"20210111" = _t, #"20210112" = _t, #"20210113" = _t]),
#"New" =  let max = Table.ColumnCount(Source) in Table.RemoveColumns(Source, {Table.ColumnNames(Source){max - 1}})
in
#"New"

 

--Nate

Syndicated - Outbound

Thank you for this. I'm still learning M and am a little confused with the 'let' here. Are you able to explain a little about how it works and why it is used here?

JollyRoger01
Helper III
Helper III

Syndicated - Outbound

I may have solved it, but if anyone has a better way please let me know. I would like to know how to hide the variable name 'max' from the Applied Steps list.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VVFLEoUwCLuLaxcC/dizON7/Go+kTZ23MKMCCQnPc5RxnIdFgjdAPqUkBMA63vBcCTf+3KvJSzve85kD/LnBWDdAFbnxk1R1f9ZOCqqwFZqFjEPqFDQWMFOxCBYteIlkJAOaunhZIh/XdsBY+3O+yp82QDW+IEz10KhBMmy7qYvF0w0I6J1hoVyafLjLhw+1cJ+4t1CKMEgOMYXQEXZoE7pmbPwVIv0zhqqsoq9AuHO4DA3pk2FGTWvXvGYoHp606aJzIypfsv+dg5tH9rzvDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"20201218" = _t, #"20201221" = _t, #"20201222" = _t, #"20201223" = _t, #"20210104" = _t, #"20210105" = _t, #"20210106" = _t, #"20210107" = _t, #"20210108" = _t, #"20210110" = _t, #"20210111" = _t, #"20210112" = _t, #"20210113" = _t]),
    max = Table.ColumnCount(Source),
    #"New" = Table.RemoveColumns(Source, {Table.ColumnNames(Source){max - 1}})
in
    #"New"

 

I would use:

= Table.RemoveColumns( PreviousStep, List.Last( Table.ColumnNames( PreviousStep ) ) )

or to make it more generic:

= Table.RemoveColumns( PreviousStep, List.LastN( Table.ColumnNames( PreviousStep ), 1 ) )

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)