Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table from excel spreadsheet and I'm trying to keep only the first and last columns, as I will be adding to it monthly. I've tried the information from this post but I cannot seem to make it work. Below is the query currently before I'm ready to select those columns. Suggestions are helpful.
let
Source = Excel.Workbook(File.Contents("\\xxxx\DoIT\SpiUsers1\KENT.LUTTRELL\Personal Items\Project Files\IES Docs\IES DB Data\IES Table Growth.xlsx"), null, true),
#"Table Growth_Sheet" = Source{[Item="Table Growth",Kind="Sheet"]}[Data],
#"Removed Blank Rows" = Table.SelectRows(#"Table Growth_Sheet", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Blank Rows",5),
#"Removed Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"Column7", "Column8"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns")
in
#"Transposed Table"
Solved! Go to Solution.
Hi
You'd have to add the following step:
let
Source = Excel.Workbook(File.Contents("\\xxxx\DoIT\SpiUsers1\KENT.LUTTRELL\Personal Items\Project Files\IES Docs\IES DB Data\IES Table Growth.xlsx"), null, true),
#"Table Growth_Sheet" = Source{[Item="Table Growth",Kind="Sheet"]}[Data],
#"Removed Blank Rows" = Table.SelectRows(#"Table Growth_Sheet", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Blank Rows",5),
#"Removed Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"Column7", "Column8"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns"),
KeepFirstAndLastColumn =
Table.SelectColumns(
#"Transposed Table",
{ List.First ( Table.ColumnNames ( #"Transposed Table" ) ),
List.Last ( Table.ColumnNames ( #"Transposed Table" ) ) }
)
in
KeepFirstAndLastColumn
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi
You'd have to add the following step:
let
Source = Excel.Workbook(File.Contents("\\xxxx\DoIT\SpiUsers1\KENT.LUTTRELL\Personal Items\Project Files\IES Docs\IES DB Data\IES Table Growth.xlsx"), null, true),
#"Table Growth_Sheet" = Source{[Item="Table Growth",Kind="Sheet"]}[Data],
#"Removed Blank Rows" = Table.SelectRows(#"Table Growth_Sheet", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Blank Rows",5),
#"Removed Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"Column7", "Column8"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns"),
KeepFirstAndLastColumn =
Table.SelectColumns(
#"Transposed Table",
{ List.First ( Table.ColumnNames ( #"Transposed Table" ) ),
List.Last ( Table.ColumnNames ( #"Transposed Table" ) ) }
)
in
KeepFirstAndLastColumn
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
yes, you can.
In general firts N and last M, in this way:
KeepFirstNAndLastMColumns =
Table.SelectColumns(
#"Transposed Table",
List.FirstN ( Table.ColumnNames ( #"Transposed Table" ),N )&
List.LastN ( Table.ColumnNames ( #"Transposed Table" ),M )
)
in
KeepFirstNAndLastMColumns
using List.RemoveRange
Cols=Table.ColumnNames ( #"Changed Type" ),
last=3,first=2,
selectedCols=List.RemoveRange(Cols,first, List.Count(Cols)-(last+first)),
KeepFirstNAndLastMColumns =
Table.SelectColumns(
#"Changed Type",
selectedCols
)
in
KeepFirstNAndLastMColumns
@Anonymous Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.