switch a few columns to bottom of other columns

[shansakhi]

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
	C	D	E	F	G	H	I	J	K	L
1	Input Data
2	DEPDATE	POS	CABIN	RBD	OND_CODE	FLT 1	FLT 2	Pax	FLT Date1	FLT Date2
3	5/18/2023	MAA	J	D	MAABLX	AY401	AY0411	1	5/18/2023	5/19/2023
4	5/18/2023	MAA	J	R	MAABLX	AY401	AY0411	2	5/18/2023	5/19/2023
5	5/19/2023	MAA	J	W	MAADXB	AY0407		1	5/19/2023	NULL
6	5/18/2023	MAA	Y	B	MAABLX	AY405	AY0409	1	5/18/2023	5/18/2023
7
8	Output Data
9	DEPDATE	POS	CABIN	RBD	OND_CODE	FLT	Pax	FLT Date
10	5/18/2023	MAA	J	D	MAABLX	AY401	1	5/18/2023
11	5/18/2023	MAA	J	R	MAABLX	AY401	2	5/18/2023
12	5/19/2023	MAA	J	W	MAADXB	AY0407	1	5/19/2023
13	5/18/2023	MAA	Y	B	MAABLX	AY405	1	5/18/2023
14	5/18/2023	MAA	J	D	MAABLX	AY0411	1	5/19/2023
15	5/18/2023	MAA	J	R	MAABLX	AY0411	2	5/19/2023
16	5/19/2023	MAA	J	W	MAADXB		1	NULL
17	5/18/2023	MAA	Y	B	MAABLX	AY0409	1	5/18/2023
Sheet1

Regards,
Shan

 

[JGordon11]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Table1
2	DEPDATE	POS	CABIN	RBD	OND_CODE	FLT 1	FLT 2	Pax	FLT Date1	FLT Date2
3	45064	MAA	J	D	MAABLX	AY401	AY0411	1	5/18/2023	5/19/2023
4	45064	MAA	J	R	MAABLX	AY401	AY0411	2	5/18/2023	5/19/2023
5	45065	MAA	J	W	MAADXB	AY0407		1	5/19/2023	NULL
6	45064	MAA	Y	B	MAABLX	AY405	AY0409	1	5/18/2023	5/18/2023
7
8	Query Output
9	DEPDATE	POS	CABIN	RBD	OND_CODE	FLT	Pax	FLT Date
10	5/18/2023	MAA	J	D	MAABLX	AY401	1	5/18/2023
11	5/18/2023	MAA	J	R	MAABLX	AY401	2	5/18/2023
12	5/19/2023	MAA	J	W	MAADXB	AY0407	1	5/19/2023
13	5/18/2023	MAA	Y	B	MAABLX	AY405	1	5/18/2023
14	5/18/2023	MAA	J	D	MAABLX	AY0411	1	5/19/2023
15	5/18/2023	MAA	J	R	MAABLX	AY0411	2	5/19/2023
16	5/19/2023	MAA	J	W	MAADXB		1
17	5/18/2023	MAA	Y	B	MAABLX	AY0409	1	5/18/2023
18
Sheet2

 

[shansakhi]

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.

 

[JGordon11]

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.

 

[shansakhi]

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}})

 

[shansakhi]

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"

 

[shansakhi]

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

 

[shansakhi]

Thank you JG.
I have done editing and its work as desired ... Thank you so much for your assistance.