Forum Discussion
Need help on Table.Selectcolumns first column through certain number of columns
Hi,
I'm new to Power BI Desktop.
I'm trying to select from 1st column upto one column before where the colummn name="Prior Quarter"
What I've done so far is below:
Get source data,
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
ColumnNameList=Table.ColumnNames(#"Promoted Headers"),---get the name of all columns
colPosition=List.PositionOf(ColumnNameList,"Prior Quarter"),---get the number of column that column name="Prior Quarter" which is 42,
What I want to do next is, keep 1st column through 42nd column, but everymonth, the "Prior Quarter" column will be changed its location. i.e. it can be 40th column or 50th column.
How can I keep columns from 1st to one column before "Prior Quarter" column?
#"Keep Only"=Table.SelectColumns(#"Promoted Headers",Table.ColumnNames(#"Promoted Headers"){0}-I'm stuck here, and not sure if I can use Table.SelectColumns.
Hi Anonymous ,
You can update the applied codes in your Advanced Editor as below(The part with red font is new added or updated ones), please find the details in the attachment.
let
Source = Excel.Workbook(File.Contents("xxxx\xxx.xlsx"), null, true),
financials_Table = Source{[Item="financials",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(financials_Table,{{"Segment", type text}, {"Country", type text}, {"Product", type text}, {"Discount Band", type text}, {"Units Sold", type number}, {"Manufacturing Price", Int64.Type}, {"Sale Price", Int64.Type}, {"Gross Sales", type number}, {"Discounts", type number}, {" Sales", type number}, {"COGS", type number}, {"Profit", type number}, {"Date", type date}, {"Month Number", Int64.Type}, {"Month Name", type text}, {"Year", Int64.Type}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
ColumnNameList=Table.ColumnNames(#"Promoted Headers"),
colPosition=List.PositionOf(ColumnNameList,"Prior Quarter"),
Custom1 = Table.FromColumns(List.Range(Table.ToColumns(#"Removed Top Rows"),0,colPosition+1)),
#"Promoted Headers1" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true])
in
#"Promoted Headers1"If the above one can't help you, could you please provide some data (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
2 Replies
Hi Anonymous ,
You can update the applied codes in your Advanced Editor as below(The part with red font is new added or updated ones), please find the details in the attachment.
let
Source = Excel.Workbook(File.Contents("xxxx\xxx.xlsx"), null, true),
financials_Table = Source{[Item="financials",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(financials_Table,{{"Segment", type text}, {"Country", type text}, {"Product", type text}, {"Discount Band", type text}, {"Units Sold", type number}, {"Manufacturing Price", Int64.Type}, {"Sale Price", Int64.Type}, {"Gross Sales", type number}, {"Discounts", type number}, {" Sales", type number}, {"COGS", type number}, {"Profit", type number}, {"Date", type date}, {"Month Number", Int64.Type}, {"Month Name", type text}, {"Year", Int64.Type}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
ColumnNameList=Table.ColumnNames(#"Promoted Headers"),
colPosition=List.PositionOf(ColumnNameList,"Prior Quarter"),
Custom1 = Table.FromColumns(List.Range(Table.ToColumns(#"Removed Top Rows"),0,colPosition+1)),
#"Promoted Headers1" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true])
in
#"Promoted Headers1"If the above one can't help you, could you please provide some data (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards