Cross Tab Query in Power Query

vibajajo64 · May 27, 2018

The data I have is in a snapshot format but I'd like to use a crosstab query to put the data in a sortable format. This is the way I'd like it to look: I've attached the excel file. Thanks for any help.

Ticker....Index................Market Cap..... Income..... Sales
abx...... DJIA.S&P500........15.74B.........574.00M.... 8.17B ................and so on
aapl..... DJIA S&P500.......947.59B........ 55.92B....247.42B................and so on

And this is the current snapshot format below which I'd like to change with a crosstab query.

Ticker

Custom.Column1

Custom.Column2

Custom.Column3

Custom.Column4

Custom.Column5

Custom.Column6

Custom.Column7

Custom.Column8

Custom.Column9

Custom.Column10

Custom.Column11

Custom.Column12

abx

Index

DJIA S&P500

P/E

27.22

EPS (ttm)

0.49

Insider Own

0.10%

Shs Outstand

1.18B

Perf Week

1.75%

abx

Market Cap

15.74B

Forward P/E

18.47

EPS next Y

0.73

Insider Trans

0.00%

Shs Float

1.16B

Perf Month

-1.69%

abx

Income

574.00M

PEG

EPS next Q

0.16

Inst Own

72.10%

Short Float

1.31%

Perf Quarter

9.57%

abx

Sales

8.17B

P/S

1.93

EPS this Y

67.00%

Inst Trans

-2.19%

Short Ratio

1.36

Perf Half Y

-5.30%

abx

Book/sh

8.12

P/B

1.65

EPS next Y

-4.10%

ROA

3.60%

Target Price

15.96

Perf Year

-17.80%

abx

Cash/sh

2.03

P/C

6.6

EPS next 5Y

-4.45%

ROE

9.60%

52W Range

11.07 - 18.35

Perf YTD

-7.46%

abx

Dividend

0.12

P/FCF

27.91

EPS past 5Y

36.10%

ROI

11.10%

52W High

-27.03%

Beta

-0.14

abx

Dividend %

0.90%

Quick Ratio

1.7

Sales past 5Y

-10.30%

Gross Margin

40.50%

52W Low

20.96%

ATR

0.27

abx

Employees

18421

Current Ratio

2.8

Sales Q/Q

-10.20%

Oper. Margin

27.00%

RSI (14)

56.07

Volatility

2.11% 1.72%

abx

Optionable

Yes

Debt/Eq

0.68

EPS Q/Q

-76.80%

Profit Margin

11.20%

Rel Volume

0.53

Prev Close

13.48

abx

Shortable

Yes

LT Debt/Eq

0.67

Earnings

Apr 23 AMC

Payout

15.30%

Avg Volume

11.19M

Price

13.39

abx

Recom

2.90

SMA20

0.0018

SMA50

2.99%

SMA200

-7.19%

Volume

5,878,240

Change

-0.67%

aapl

Index

DJIA S&P500

P/E

17.37

EPS (ttm)

10.85

Insider Own

0.06%

Shs Outstand

5.02B

Perf Week

1.22%

aapl

Market Cap

947.59B

Forward P/E

14.21

EPS next Y

13.27

Insider Trans

-10.36%

Shs Float

4.91B

Perf Month

14.83%

aapl

Income

55.92B

PEG

1.29

EPS next Q

2.18

Inst Own

61.90%

Short Float

0.94%

Perf Quarter

5.37%

aapl

Sales

247.42B

P/S

3.83

EPS this Y

10.80%

Inst Trans

-

Short Ratio

1.44

Perf Half Y

7.78%

aapl

Book/sh

25.25

P/B

7.47

EPS next Y

15.53%

ROA

14.30%

Target Price

197.02

Perf Year

22.56%

aapl

Cash/sh

17.50

P/C

10.78

EPS next 5Y

13.45%

ROE

40.00%

52W Range

142.20 - 190.37

Perf YTD

11.43%

aapl

Dividend

2.92

P/FCF

20.16

EPS past 5Y

7.90%

ROI

18.30%

52W High

-0.94%

Beta

1.29

aapl

Dividend %

1.55%

Quick Ratio

1.4

Sales past 5Y

7.90%

Gross Margin

38.30%

52W Low

32.62%

ATR

3.18

aapl

Employees

123000

Current Ratio

1.5

Sales Q/Q

15.60%

Oper. Margin

26.70%

RSI (14)

67.04

Volatility

1.27% 1.64%

aapl

Optionable

Yes

Debt/Eq

0.96

EPS Q/Q

30.10%

Profit Margin

21.50%

Rel Volume

0.53

Prev Close

188.15

aapl

Shortable

Yes

LT Debt/Eq

0.8

Earnings

May 01 AMC

Payout

24.20%

Avg Volume

32.22M

Price

188.58

aapl

Recom

2.00

SMA20

0.0309

SMA50

7.33%

SMA200

11.58%

Volume

17,225,551

Change

0.23%

Rudi · Jun 1, 2018

I have no idea if this is what you need...but what about this:

Code:

let
    Source = {"abx","aapl","goog","hl","vz","pvh","amzn","snap","fb","nvda"},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Ticker"}}),
    #"Get Data" = Table.AddColumn(#"Renamed Columns", "Custom", each GetData([Ticker])),
    #"Transpose Data" = Table.AddColumn(#"Get Data", "Transpose", each Table.PromoteHeaders(Table.Transpose([Custom]), [PromoteAllScalars=true])),
    #"Removed Columns" = Table.RemoveColumns(#"Transpose Data",{"Custom"}),
    #"Expanded Transpose" = Table.ExpandTableColumn(#"Removed Columns", "Transpose", {"Index", "Market Cap", "Income", "Sales", "Book/sh", "Cash/sh", "Dividend", "Dividend %", "Employees", "Optionable", "Shortable", "Recom"}, {"Index", "Market Cap", "Income", "Sales", "Book/sh", "Cash/sh", "Dividend", "Dividend %", "Employees", "Optionable", "Shortable", "Recom"})
in
    #"Expanded Transpose"

Cross Tab Query in Power Query

vibajajo64

New member

Attachments

Rudi

New member