Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

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

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...

5 years ago

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
5 years ago
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

How to combine column 1 and column 2 such that all the elements in column 2 move below column 1.pbix12 KB
Jimmy801
5 years ago
Hello Anonymous

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

BR

Jimmy

AlB

Community Champion

5 years ago

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

Not applicable

5 years ago

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

Community Champion

5 years ago

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
Not applicable
5 years ago
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
  Community Champion
  5 years ago
  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

Not applicable

5 years ago

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

Community Champion

5 years ago

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
Not applicable
5 years ago
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],

Forum Discussion

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

Related content

Combine Column

Combining Column Data

Combine columns into 1

Tables combine

Combine list of elements