How to combine column 1 and column 2 such that all the elements in column 2 move below column 1?

[Anonymous]

Hi everyone, 

I want to combine variable, variable1 and variable2 into one column and value, value1 and value2 into another column which means the total number of rows will be 36. So at the end of the table it will only have 2 columns which is variable and value. May I know how should I do? Thank you.

[AlB · Accepted solution]

@Anonymous 

Create a function from the code above (named  "function_")

(inputT_ as table)=> 
let
    colNames_ = Table.ColumnNames(inputT_),
    colNum_ = Table.ColumnCount(inputT_),
    auxList_ = List.Numbers(0,colNum_/2),
    auxList2_ = List.Transform(auxList_, each Table.RenameColumns(Table.SelectColumns(inputT_,List.Range(colNames_, _*2,2)),{{colNames_{_*2}, "Value"}, {colNames_{_*2+1}, "Variable"}})), 
    TFinal_ = Table.Combine(auxList2_)
in
    TFinal_

 Then you can apply it on the query you showed above:

let
 Source = Web.Page(Web.Contents("https://finviz.com/quote.ashx?t=FB&ty=c&p=d&b=1")),
 res_ = function_ (Source{0}[Data])
in
 res_

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

See it all at work in the attached file

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Jimmy801 · Accepted solution]

Hello @Anonymous 

did any of the answer suit your requirements? If yes, please mark them as solution please

BR

Jimmy

[AlB · Accepted solution]

@Anonymous 

See it all at work in the attached file

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

Create a function from the code above (named  "function_")

(inputT_ as table)=> 
let
    colNames_ = Table.ColumnNames(inputT_),
    colNum_ = Table.ColumnCount(inputT_),
    auxList_ = List.Numbers(0,colNum_/2),
    auxList2_ = List.Transform(auxList_, each Table.RenameColumns(Table.SelectColumns(inputT_,List.Range(colNames_, _*2,2)),{{colNames_{_*2}, "Value"}, {colNames_{_*2+1}, "Variable"}})), 
    TFinal_ = Table.Combine(auxList2_)
in
    TFinal_

 Then you can apply it on the query you showed above:

let
 Source = Web.Page(Web.Contents("https://finviz.com/quote.ashx?t=FB&ty=c&p=d&b=1")),
 res_ = function_ (Source{0}[Data])
in
 res_

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Anonymous 

This  can probably be done with pivot/unpivot.

Can you share the original table in text format (instead of a screen pic) so that it can be copied

And show also the expected result, what the final table should look like

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

VariableValueVariable1Value1Variable2Value2Column7Column8Column9Column10Column11Column12

Index	S&P 500	P/E	32.82	EPS (ttm)	8.43	Insider Own	0.63%	Shs Outstand	2.85B	Perf Week	1.46%
Market Cap	790.44B	Forward P/E	26.39	EPS next Y	10.49	Insider Trans	-9.62%	Shs Float	2.39B	Perf Month	-0.37%
Income	25.28B	PEG	1.98	EPS next Q	3.19	Inst Own	79.90%	Short Float	0.97%	Perf Quarter	5.73%
Sales	78.98B	P/S	10.01	EPS this Y	-15.10%	Inst Trans	-0.25%	Short Ratio	1.25	Perf Half Y	21.89%
Book/sh	41.31	P/B	6.7	EPS next Y	12.21%	ROA	18.10%	Target Price	321.15	Perf Year	35.40%
Cash/sh	19.48	P/C	14.21	EPS next 5Y	16.60%	ROE	23.30%	52W Range	137.10 - 304.67	Perf YTD	34.85%
Dividend	-	P/FCF	41.19	EPS past 5Y	42.20%	ROI	17.40%	52W High	-9.15%	Beta	1.19
Dividend %	-	Quick Ratio	5.5	Sales past 5Y	41.50%	Gross Margin	81.00%	52W Low	101.88%	ATR	7.17
Employees	56653	Current Ratio	5.5	Sales Q/Q	21.60%	Oper. Margin	36.40%	RSI (14)	52.02	Volatility	2.46% 2.71%
Optionable	Yes	Debt/Eq	0	EPS Q/Q	28.10%	Profit Margin	32.00%	Rel Volume	0.88	Prev Close	277.00
Shortable	Yes	LT Debt/Eq	0	Earnings	Oct 29 AMC	Payout	0.00%	Avg Volume	18.64M	Price	276.78
Recom	1.80	SMA20	-0.0006	SMA50	0.31%	SMA200	14.74%	Volume	16,361,201	Change	-0.08%

[Jimmy801]

Hello @Anonymous 

you can try thi dynamically approach.

First exctract all value and variable columns and transform this list into the real values of you columns. Then combine this two list into one and create with this data your final table. Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZRrb+owDIb/SjQJaZNYSNL7R8ZgQxriqjNNO/uQQQYVXctJwy7//jhOCj0XCUFsXD+287rPzxc/pM7la6EuunAsju7XuXjj4y2naJyi5Qwapz2cPy/d54txuVFfYCx/HhkT8YxEjIE56w3hOxA0tXmGsyW5NOb9Cs4pDW2WcVnnG6XJ9LMEi9E46Ngsu5pMj6Y2styACY9HNzab0m/kUak9nDkN4w6iJ1LvlSEDeQB3kjEahjZ4VOlPqTfElSBiGmS+hFJ9GfJkc0Bs1ipipWVZg32d0Vg0dYyKShosIshORUyq0uxsJKNB0vETWFfvdq4ioiLFyOEdFpqlbfDcDoRyzzW+8ySjGXPISpsTlNEs6TTQ+VFqozSYEU0CR13KQtmSkxQwCO0tXWeMe6rZ5TW2e80jyhGC4FO3jIrojF5Ik1dYt4ga8r0s3jCF4DTNHPmmqva92g4h5DTgiLYFxDT5Z86CCm4Ji2nfmqkvYyX1Fm5upvO1Qplwyk/MJyVtq0FEQ+aIA1nvHJFnNEyROLBWCOnbzAihMY2Zg6IAAhqgGYlH6LHcWiIPEiiFXJOAhTROTujVrSWHIDtHvs0/QCCoxWvEjgYj1zhvRHWQtQeH0K0Hjy0jwQYc+D7f7pzAOI78RhmJs4Y8bRDpeNT8mK/3pzuJqB0P3nkbyGmEhDtd1TWBfdjmVlMpp+xEfqg+URdwgan19VcLKxvKEwQP3w9F9a1QS1EcR3Y3B0etVWn+S5/35k4ObsbTg9L0TA5i3/NiOSaXPLzCIijDl0lVQL4iN9+4VLDFRNCEu0FPD4Aq/YvqCau5Va+mN/xll8GP2rMbFc109ZabFlz4theqIEA74lYy6BuD1QcZFFWNm5okEOr2yGr/D+7DivyFlrrMy639b7o2RGSkP7Hym8nv6uiW1XH7H9szF8QehxMkO5WLBDYkRehCwSsDrz+1gOWkL5hbSMZY7DwRw8wB7g9GMCf5JOy4aXpO3A1i3hW49oOdF7hNBdf98vIb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}, {"(blank).8", type text}, {"(blank).9", type text}, {"(blank).10", type text}, {"(blank).11", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Variable", type text}, {"Value", type text}, {"Variable1", type text}, {"Value1", Int64.Type}, {"Variable2", type text}, {"Value2", type text}, {"Variable3", type text}, {"Value3", Percentage.Type}, {"", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}}),
    GetVariables = List.Combine(List.Transform(List.Select(Table.ColumnNames(#"Changed Type1"), each Text.Contains(_, "Variable")), each Table.Column(#"Changed Type1", _))),
    GetValues =List.Combine(List.Transform(List.Select(Table.ColumnNames(#"Changed Type1"), each Text.Contains(_, "Value")), each Table.Column(#"Changed Type1", _))),
    CreateFinalTable= Table.FromColumns
    (
        {
            GetVariables,
            GetValues
        },
        {
            "Variables",
            "Values"
        }
    )
in
    CreateFinalTable

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi Jimmy, thanks for your solution! Sorry to say that I actually need to loop through different stocks by making this as a query function. So the first step I planned is to merged all the variables into one column and value in another column. In fact, there are more than 3 variables and values. I actually scrap the data from the website : https://finviz.com/quote.ashx?t=FB&ty=c&p=d&b=1

The code below is my code for now:

let
Source = Web.Page(Web.Contents("https://finviz.com/quote.ashx?t=FB&ty=c&p=d&b=1")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type number}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", Percentage.Type}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
in
#"Changed Type"

[Jimmy801]

Hello @Anonymous 

here the function of my code.(not tested). This takes a table and combines all "Variable"-columns and all "Value"-columns into one single column for each type. Be aware that Power Query is case sensitive

(t as table) as table => 
let
    Source = t
    GetVariables = List.Combine(List.Transform(List.Select(Table.ColumnNames(Source ), each Text.Contains(_, "Variable")), each Table.Column(Source , _))),
    GetValues =List.Combine(List.Transform(List.Select(Table.ColumnNames(Source ), each Text.Contains(_, "Value")), each Table.Column(Source , _))),
    CreateFinalTable= Table.FromColumns
    (
        {
            GetVariables,
            GetValues
        },
        {
            "Variables",
            "Values"
        }
    )
in
    CreateFinalTable

 Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@AlB Sorry, I'm new to power query and this forum, is what I did correct? Basically I just scrapped the table from this website: https://finviz.com/quote.ashx?t=FB&ty=c&p=d&b=1

[AlB]

@Anonymous 

Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZLfb4IwEMf/lYbEZEu0tIUiPCriYjIjyl6M86FKM4gKDOrm/vtdCzPO7YX70e/n7nLHZmPNilRerL6VvJ4JYV6MOCEQxnYEX4dhn4GN4gQ9KHV6BN/HrmNt+xtrLuqDVCgUFWSHAcGuOwZvWtafok5RW4F52Am6CoW8KLSGgII2MDVmxb48Sa3jmPkaj6MnrcCBf0st9TCYtlAijrLRPX1QGcZO2qqEdpDK8sa0GlCOKekZblyWB7vJIOtS7FADatzDwz8TMsxoS4WiyVqKBtj1DRXqyAXJLccN6GGvazfJP/JUFqmewlDTcNr2pj8bqUTTcS40vONQryOX53x/QCuh8hIijrk+l97BLU8x7/joVB3LL2lWxD2PO2DDc13LQv1bZGnr7TJ6HXxRgagQu6M+zNrUmcidsqN38Eg3ekf51+0mWVmrX9DzC7rjRF3kxZt+W+wVYgEazUMDryT8B+buvhYm8xHTdgAnJcRrM1xnCBwO+m2/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Variable = _t, Value = _t, Variable1 = _t, Value1 = _t, Variable2 = _t, Value2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Variable", type text}, {"Value", type text}, {"Variable1", type text}, {"Value1", Int64.Type}, {"Variable2", type text}, {"Value2", type text}}),


    colNames_ = Table.ColumnNames(#"Changed Type"),
    colNum_ = Table.ColumnCount(#"Changed Type"),
    auxList_ = List.Numbers(0,colNum_/2),
    auxList2_ = List.Transform(auxList_, each Table.RenameColumns(Table.SelectColumns(#"Changed Type",List.Range(colNames_, _*2,2)),{{colNames_{_*2}, "Value"}, {colNames_{_*2+1}, "Variable"}})), 
    TFinal_ = Table.Combine(auxList2_)
in
    TFinal_

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB Seems like the original source of data already been replaced? If this is the case then I'm not able to do a for loop to loop through different stock right? Is there any way to maintain this line of code shown below) so that I can change the "FB" to a parameter and able to loop through different stocks?

Source = Web.Page(Web.Contents("https://finviz.com/quote.ashx?t=FB&ty=c&p=d&b=1")),
Data0 = Source{0}[Data],