Power BI Visulization Query

[shansakhi]

Hi All,
I am trying to build a report in Power BI, here I need your support to guide me how I can achieve the required visulization.

Here W1 is our comany.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3
4		Input				Output
5		Bus Route	Fare ($)	Bus Company		Bus Route	W1 (Our Company) Fare	Min (Lowest) Fare	% Lower than W1	Other Bus Company >>>>>> Lower to Higher fares excluding W1
6		BOM-DEL	20	W1 (Our Company)		BOM-DEL	20	14	-30%	F10 / 14	C5 / 25	A10 / 30
7		BOM-DEL	25	C5		BOM-BLR	22	20	-9%	H19 / 20	K1 / 25	E10 / 33	Y12 / 38	J25 / 48
8		BOM-DEL	14	F10
9		BOM-DEL	30	A10
10		BOM-BLR	28	W1 (Our Company)
11		BOM-BLR	33	E10
12		BOM-BLR	20	H19
13		BOM-BLR	38	Y12
14		BOM-BLR	48	J25
15		BOM-BLR	25	K1
16
Sheet1

Cell Formulas
Range	Formula
J6:J7	J6	=I6/H6-1

Regards,

 

[alex78]

a proposal close to your request

let
A = Excel.CurrentWorkbook(){[Name="Inpout"]}[Content],
    B = {"Bus Route", "W1 (Our Company) Fare", "Min (Lowest) Fare", "% Lower than W1", "Other Bus Company >>>>>>" & "#(lf)" & "Lower to Higher fares excluding W1"},
C = Table.Combine(Table.Group(A, {"Bus Route"}, {"x", each 
[ 
 a = List.Zip(List.Skip(Table.ToColumns(_))),
 b = List.Select(a, each _{1} = "W1 (Our Company)"),
 c = List.Sort(a, each _{0}),
 d = let x = {b{0}{0}, c{0}{0}} in {Table.FirstValue(_)} & x & {x{1}/x{0} -1} & List.Transform({List.Transform(List.RemoveItems(c,b), each Text.From( _{1}) & " / $" & Text.From (_{0}))}, each Text.Combine(_," < ")),
 e = Table.FromRows({d},B)
] [e]})[x]) 
in C

Regards,

 

[shansakhi]

Thank you.
But unfortunalty I am not sure how do I use this in POwer BI.
Can you please guide me.

Thank you.

 

[JEC]

You can use that Mcode in the power query editor behind PowerBI

 

[shansakhi]

I have uploaded the data in Power BI and then as a new query I have copied the above code... but I am getting below ..

 

[JEC]

you have to paste that code in the advanced editor

 

[shansakhi]

I think, I am doing somthing wrong.
I am trying to add A= file location and sheet name, but getting error.
Because with the earlier provided code, I am not getting the excel file.

let
[B]A = Excel.Workbook("D:\BusRoute_PB_workring.xlsx"){[Name="Sheet3"]}[Content],[/B]

    B = {"Bus Route", "W1 (Our Company) Fare", "Min (Lowest) Fare", "% Lower than W1", "Other Bus Company >>>>>>" & "#(lf)" & "Lower to Higher fares excluding W1"},
C = Table.Combine(Table.Group(A, {"Bus Route"}, {"x", each
[
 a = List.Zip(List.Skip(Table.ToColumns(_))),
 b = List.Select(a, each _{1} = "W1 (Our Company)"),
 c = List.Sort(a, each _{0}),
 d = let x = {b{0}{0}, c{0}{0}} in {Table.FirstValue(_)} & x & {x{1}/x{0} -1} & List.Transform({List.Transform(List.RemoveItems(c,b), each Text.From( _{1}) & " / $" & Text.From (_{0}))}, each Text.Combine(_," < ")),
 e = Table.FromRows({d},B)
] [e]})[x])
in C