switch a few columns to bottom of other columns

shansakhi

Active Member
Joined
Apr 5, 2008
Messages
292
Office Version
  1. 365
Platform
  1. Windows
Hello Power BI Experts,
I am working on a data where I would like to switch a few columns to bottom of other columns.
Actually it's difficult for me to explain hence I colored the data for easy understanding.

1. Green color data to be repeated
2. Orange color data to be considered first
3. Blue color data to be considered later

Request your assistance/guidance to accomplish this in Power BI. Thank you,

Book2
CDEFGHIJKL
1Input Data
2DEPDATEPOSCABINRBDOND_CODEFLT 1FLT 2PaxFLT Date1FLT Date2
35/18/2023MAAJD MAABLXAY401AY041115/18/20235/19/2023
45/18/2023MAAJR MAABLXAY401AY041125/18/20235/19/2023
55/19/2023MAAJW MAADXBAY040715/19/2023NULL
65/18/2023MAAYB MAABLXAY405AY040915/18/20235/18/2023
7
8Output Data
9DEPDATEPOSCABINRBDOND_CODEFLTPaxFLT Date
105/18/2023MAAJD MAABLXAY40115/18/2023
115/18/2023MAAJR MAABLXAY40125/18/2023
125/19/2023MAAJW MAADXBAY040715/19/2023
135/18/2023MAAYB MAABLXAY40515/18/2023
145/18/2023MAAJD MAABLXAY041115/19/2023
155/18/2023MAAJR MAABLXAY041125/19/2023
165/19/2023MAAJW MAADXB1NULL
175/18/2023MAAYB MAABLXAY040915/18/2023
Sheet1


Regards,
Shan
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"DEPDATE", type date}, {"POS", type text}, {"FLT 1", type text}, {"FLT 2", type text},
        {"OND_CODE", type text}, {"RBD", type text}, {"CABIN", type text}, {"Pax", Int64.Type}, {"FLT Date1", type date}, {"FLT Date2", type date}}),
    tbl1 = Table.RenameColumns(Table.RemoveColumns(ChangeType,{"FLT 2", "FLT Date2"}), {{"FLT 1", "FLT"}, {"FLT Date1", "FLT Date"}}),
    tbl2 = Table.RenameColumns(Table.RemoveColumns(ChangeType,{"FLT 1", "FLT Date1"}), {{"FLT 2", "FLT"}, {"FLT Date2", "FLT Date"}}),
    Result = tbl1 & tbl2
in
    Result

Book1
ABCDEFGHIJK
1Table1
2DEPDATEPOSCABINRBDOND_CODEFLT 1FLT 2PaxFLT Date1FLT Date2
345064MAAJD MAABLXAY401AY041115/18/20235/19/2023
445064MAAJR MAABLXAY401AY041125/18/20235/19/2023
545065MAAJW MAADXBAY040715/19/2023NULL
645064MAAYB MAABLXAY405AY040915/18/20235/18/2023
7
8Query Output
9DEPDATEPOSCABINRBDOND_CODEFLTPaxFLT Date
105/18/2023MAAJD MAABLXAY40115/18/2023
115/18/2023MAAJR MAABLXAY40125/18/2023
125/19/2023MAAJW MAADXBAY040715/19/2023
135/18/2023MAAYB MAABLXAY40515/18/2023
145/18/2023MAAJD MAABLXAY041115/19/2023
155/18/2023MAAJR MAABLXAY041125/19/2023
165/19/2023MAAJW MAADXB1
175/18/2023MAAYB MAABLXAY040915/18/2023
18
Sheet2
 
Upvote 1
Solution
Thank you JG.
But how do I insert above query in my power bi.
Loading the file via get Data in excel and using transforming data ... not using direct Power BI.
Attached image for your reference.
 

Attachments

  • Test1.PNG
    Test1.PNG
    62.6 KB · Views: 7
Upvote 0
I would need to see the underlying code. Open the advanced editor and copy the code there, and paste in a reply.

Or if you want to try to edit it yourself append the last three steps of my code to yours and change the table names to match your code.
 
Upvote 0
I would need to see the underlying code. Open the advanced editor and copy the code there, and paste in a reply.

Or if you want to try to edit it yourself append the last three steps of my code to yours and change the table names to match your code.
Source: = Excel.Workbook(File.Contents("C:\Desktop\Test_Power BI.xlsx"), null, true)
Navigation= Source{[Item="Sheet4",Kind="Sheet"]}[Data]
Promoted Headers= Table.PromoteHeaders(Sheet4_Sheet, [PromoteAllScalars=true])
Change Type = Table.TransformColumnTypes(#"Promoted Headers",{{"DEPDATE", type date}, {"POS", type text}, {"CABIN", type text}, {"RBD", type text}, {"OND_CODE", type text}, {"FLT 1", type text}, {"FLT 2", type text}, {"Pax", Int64.Type}, {"FLT Date1", type date}, {"FLT Date2", type any}})
 
Upvote 0
Sorry .. here is the code from advance editor.

let
Source = Excel.Workbook(File.Contents("C:\Desktop\Test_Power BI.xlsx"), null, true),
Sheet4_Sheet = Source{[Item="Sheet4",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet4_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"DEPDATE", type date}, {"POS", type text}, {"CABIN", type text}, {"RBD", type text}, {"OND_CODE", type text}, {"FLT 1", type text}, {"FLT 2", type text}, {"Pax", Int64.Type}, {"FLT Date1", type date}, {"FLT Date2", type any}})
in
#"Changed Type"
 
Upvote 0
I tried editing the query in editor as below but getting error


let
Source = Excel.Workbook(File.Contents("C:\Desktop\Test_Power BI.xlsx"), null, true),

Sheet4_Sheet = Source{[Item="Sheet4",Kind="Sheet"]}[Data],

#"Promoted Headers" = Table.PromoteHeaders(Sheet4_Sheet, [PromoteAllScalars=true]),

ChangeType = Table.TransformColumnTypes(Source,{{"DEPDATE", type date}, {"POS", type text}, {"FLT 1", type text}, {"FLT 2", type text},
{"OND_CODE", type text}, {"RBD", type text}, {"CABIN", type text}, {"Pax", Int64.Type}, {"FLT Date1", type date}, {"FLT Date2", type date}}),

tbl1 = Table.RenameColumns(Table.RemoveColumns(ChangeType,{"FLT 2", "FLT Date2"}), {{"FLT 1", "FLT"}, {"FLT Date1", "FLT Date"}}),

tbl2 = Table.RenameColumns(Table.RemoveColumns(ChangeType,{"FLT 1", "FLT Date1"}), {{"FLT 2", "FLT"}, {"FLT Date2", "FLT Date"}}),

Result = tbl1 & tbl2
in
Result

Error
Expression.Error: The column 'DEPDATE' of the table wasn't found.
Details:
DEPDATE
 
Upvote 0
Thank you JG.
I have done editing and its work as desired ... Thank you so much for your assistance.
 
Upvote 0

Forum statistics

Threads
1,223,348
Messages
6,171,572
Members
452,411
Latest member
colpie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top