Sid29

1

votes
0

answer
28

views

SharePoint Tables - Build URL using Project ID

I am trying to connect to a Sharepoint project. This is the function that I have created. (ReqURL as text, ReqTitle as text) => let Source = SharePoint.Tables(ReqURL, [ApiVersion = 15]), ReqData = Source{[Title=ReqTitle]}[Items] in ReqData https://XXXX.sharepoint.com/sites/sandbox100pwa/projectA/ Bu...
Sid29
1

votes
1

answer
173

views

Power BI - Adding/Subtracting to the Working Hours using DAX

I have two tables. Calendar Table Date Holiday 1/1/2019 Yes 1/2/2019 No 1/3/2019 No 1/4/2019 Yes 1/5/2019 No 1/6/2019 Yes 1/7/2019 No . . Data Table Request # CreatedDateTime ClosedDateTime A1 1/2/2019 7:15:00 AM 1/4/2019 7:15:00 AM A2 1/2/2019 5:...
Sid29
1

votes
1

answer
74

views

Pandas - Read a table from excel

I am trying to read a table from excel in Pandas. But, I am not able to do it. For example, the below code works for me and it reads the data from Sheet1 on File.xlsx file df = pd.read_excel('File.xlsx', sheetname='Sheet1') But there is a table in that sheet called as 'Table4' and I want to read tha...
Sid29
1

votes
1

answer
185

views

Excel VBA - Selecting Columns dynamically using Column headers

I am trying to select a column dynamically based on the Column headers in VBA. This is the code that I am using , Sub trial() Dim colm As Long Dim nwb As Workbook, wb As Workbook Dim nwk As Worksheet, wk As Worksheet, wk1 As Worksheet co1m = WorksheetFunction.Match('Header', Sheets('Sheet1').Rows(1)...
Sid29
1

votes
1

answer
1.1k

views

VBA to open an Excel File

I am trying to open an excel file in a folder of excel files using VBA. I direct my code to take the end user straight to the folder and allow him to choose the file from the dialog box. But I am not able to open the file even after selecting it from the dialog box. My understanding of the problem...
Sid29
1

votes
1

answer
96

views

Python Selenium - Webscrape Latitude and Longitude from the Maps

I am trying to webscrape the latitude and longitude from the Maps in the URL The HTML Script looks below, $(document).ready(function() { var $injector = angular.element(document.body).injector(); var $compile = $injector.get('$compile'); var $rootScope = $injector.get('$rootScope'); var sfMap = $com...
Sid29
1

votes
2

answer
85

views

Appending a new row to an existing dataframe

I am trying to insert a new record to a data frame and am getting this error - DataFrame constructor not Properly called. This is my code, import pandas as pd #defining the dataframe dfHeader = { 'Name': [], 'Daily': [],'Weekly': [],'Monthly': [],'Yearly': [], 'Area': [], 'Latitude':[] , 'Longitude'...
Sid29
1

votes
1

answer
83

views

Pyodbc - loading data inside a table causing error

I am trying to load the data inside the table trial and it says Invalid Column name - Name. I am passing values inside Name and Area dynamically. cursor.execute('insert into trial (NameofTheProperty, AreaofTheProperty) values (Name, Area)') cnxn.commit()
Sid29
1

votes
1

answer
637

views

Power Query - Rename columns in Query edior

I have a requirement where the name of the column in the report changes dynamically and I am trying to rename it in the advanced editor in PowerBI. Renamed Columns' = Table.RenameColumns(#'Added Conditional Column1',{{'AgingReport_201801022101.txt', 'Report Name'}, {'1/3/2018 2:38:13 AM', 'Report Cr...
Sid29
1

votes
1

answer
1.8k

views

PowerBI - Calculate the number of occurance of a value in a column using calculated field

I am trying to calculate the number of times a value occurs in a column by using calculated field. Measure can do this a bit more easily, but measure limits my usage for creating other columns and the usages in the dashboards. City BOS DTW CLE CLE BOS BOS Expected Output: City Total BOS 3 CLE...
Sid29
1

votes
1

answer
547

views

PowerBI - Count the number of blanks for a Column Value after filtering by that column value

I have a requirement where I am trying to count the number of blank rows for a particular column value. For Example, Table 1 ColA ColB A as A B ab B B C C And my expected output is Table1 ColA ColB output A as 1 A 1 B ab 2 B 2 B 2 C...
Sid29
1

votes
2

answer
333

views

R - Building a dataframe using Country,continent, latitude and longitude

I have a dataframe that has a list of countries on one column and I am looking to get the Continent Name, Latitude and Longitude of that country. For example, df
Sid29
1

votes
1

answer
2.5k

views

FileFormat:= 51 or 52 doesn't save file as an excel file

I am saving a file using the command FilNam = '//drive/folder/Test' File.SaveAs FileName:=FilNam, FileFormat:=51 File.SaveAs FileName:=FilNam, FileFormat:=52 Here is how it appears But I am able to select this file and open with excel to see the data inside them. I would want this to appear as en e...
Sid29
1

votes
1

answer
30

views

PowerBI Desktop - Identifying Common Items that belongs to the Selected Values in a column

I am trying to do some analytics on PowerBI. I am struck at this logic, where I am trying to Identify the common items with in the group and not common items with in the group. For Example, Group Name Contact GroupA Rock GroupA Eddy GroupA Brown GroupB Rock GroupB Katie G...
Sid29
1

votes
1

answer
702

views

Power BI - Calculate Percentage Measure based on Top N Selection

I have a table like this. Company Amount Year A 200 2016 B 300 2016 C 400 2016 A 500 2017 B 600 2017 C 700 2017 A 100 2016 B 400 2016 C 100 2016 A 600 2017 B 133 2017 C 50 2017 I am looking for a measure calculate the Percentage of amount that top 2 companies(based on amount...
Sid29
1

votes
2

answer
114

views

What is the R equivalent for Python's .cat.codes, which converts categorical variable to integer levels?

In python, you can generate a categorical code for a variable using .cat.code e.g. df['col3'] = df['col3'].astype('category').cat.code How do you do this in R ?
Sid29
1

votes
1

answer
369

views

PowerBI - Direct Query DAX Model not Supporting Min function

I am building a calculated column in PowerBI to identify the Month Number. For Example Jan 2017 will be 1 and Jan 2018 will be 13 and not 1. To do this, I usually do a calculated column like this :- MonthNumber = DATEDIFF(Min(Orders[Date]),Orders[Date],MONTH) But Min function is not supported for D...
Sid29
1

votes
1

answer
323

views

Power BI - Incrementing Week Numbers across years using M-Query

I have a column called as Submitted_Date in my table. I am looking to create a column called as Week Number in my table. This Week Number should be incrementing across the years. It should take the minimum date across the column date and take the first date of that particular year to start week numb...
Sid29
1

votes
1

answer
541

views

Power BI - Creating a measure as a Visual Level Filter

I have a requirement, where I have data like this. ColA ColB ColC ColD A A C 1 B C C 1 A B C 3 C D C 2 I have one visual as a table with just Col A and Col B generated through the Min Number on Col D. ColA ColB A A B c I want to generate another...
Sid29
1

votes
1

answer
31

views

Importing .xls file that is saved as *.htm, *.html as it is saved on the backend

I have a requirement where I have to import an .xls file which is saved as .*htm, .*html. How do we load this inside R in a data frame. The data is present in Sheet1 starting from Row Number 5. I have been struggling with this by trying to load it using xlsx package and readxl package. But neither o...
Sid29
1

votes
1

answer
0

views

Python Tensor Flow Training- Value Error: Tensor must be from the same graph as Tensor

I have a fruit_train_net.py file that has the following code import tensorflow as tf import numpy as np import time import os import re tf.reset_default_graph() from Pythonfiles import fruit_network as network from Pythonfiles import utils from Pythonfiles import constants # default number of iterat...
Sid29
1

votes
1

answer
0

views

Python String Replace - “\” by “/”

I am trying to replace a string here in Python. This is my Input - MyString = \\ServerA\DriveB\5.FolderC\A.TXT I want my output to be like this OutputString = //ServerA/DriveB/5.FolderC/A.TXT I tried the replace method it didn't work. Is there a function that can convert it ? Kindly help me with thi...
Sid29
1

votes
1

answer
0

views

Pandas - Reordering columns by String contains concept and doing a compare between them

I have a pandas data frame like this. ID Name Gender Work BBBB.FullName BBBB.Sex BBBB.Occupation 1 Test M MIS Tester M GIS And I want this to be re-ordered to this way, ID always comes first The first available column without starting wit...
Sid29
1

votes
1

answer
0

views

Pass a value dynamically to the declaring object/variable

I am trying to assign a name of the dataframe dynamically and I am failing. The code below assigns it to the variable DataFrame_Name and not the value of the DataFrame_Name for i in range(0, len(Required_Reports_df)): ReportName = Required_Reports_df.iloc[i]['Report Name'] ReportPath = Required_Rep...
Sid29
1

votes
2

answer
0

views

Python - Replace NA's on Joins not working

I am trying to fill the values of a NA with some default text values. Here is my df1 data = [['Alex','10'],['Bob','12'],['Clarke','13']] df1 = pd.DataFrame(data,columns=['Id','Age']) Here is my df2 data = [['Alex','10'],['Clarke','13']] df2 = pd.DataFrame(data,columns=['Id','Age']) Here is my df3 da...
Sid29
1

votes
2

answer
42

views

Excel VBA - Objtdict.Keys Produces an error when range is changed

I am trying to generate an Unique list from a column. My Code works fine when the range is B1:B but when it is changed to B17:B it produces NA's and outputs the values in irrelevant Rows inside the column B. Here is the Code, Sub A_Unique_B() Dim X Dim objDict As Object Dim lngRow As Long Set objDic...
Sid29
1

votes
1

answer
239

views

Excel VBA - Auto FIlter and Advanced filter usage error

I have a requirement where in, I need to use the auto filter to filter the data first and then am using the advanced filter to get the Unique values alone. But the advanced filter doesn't take the auto filtered value alone. How do I use them together? Here goes my code, Colmz = WorksheetFunction.Mat...
Sid29
1

votes
1

answer
3.9k

views

VBA download a File from OneDrive

I am trying to download a file from OneDrive. I am new to API Concepts and here is Code, Sub OneDrive_Download() 'Declare the Object Dim oRequest As Object 'Create and Assign Object Set oRequest = CreateObject('MSXML2.XMLHTTP') 'Input User OneDrive URL URL = 'https://xxxx-my.sharepoint.com/personal/...
Sid29
1

votes
1

answer
26

views

Delete a ListBox using VBA

I am trying to delete the ListBox using VBA. But, I am not able to get it deleted. This is my Code, Sub Del_FListBox() Dim FListBox As ListBox ThisWorkbook.Sheets('Home').FListBox.Delete End Sub Kindly share your thoughts.
Sid29
1

votes
1

answer
186

views

Month Number Incrementation using measure in PowerBI

I have a requirement, where I need to Increment the Month Number. For Example, Jan 2016 - Dec 2016 is 1 - 12 and Jan 2017 Starts at 13. Jan 2018 Starts at 25. How do we create a measure for this in PowerBI ? Thanks.
Sid29
1

votes
1

answer
1.4k

views

PowerBI - Get the Index of the First Occurance of a value in the column

I am trying to return the Index of a first occurrence of a value in a column. I would want to use the Calculated Column functionality in PowerBI. For Example, Input Output ASD 1 ASD 1 ASD 1 GEF 4 GEF 4 HIJ 6 GEF 4 This can be...
Sid29
1

votes
1

answer
56

views

Userform doesn't appear until I click on the Worksheet

I am loading an userform through a button click and this userform is not showing up in the front until I click on the workbook. Excel icon keeps blinking and when I click on the worksheet, the blinking stopped and the userform appeared. what could be the possible reason. It's strange because the sam...
Sid29
1

votes
1

answer
37

views

Access Shell - not getting closed

I am using vba in excel to get/write from an access db. I am declaring objects and setting them values in the beginning of the program. There is also a piece of code in the end, that closes the connection. But the access shell still appears. Here is a copy of the code, Application.ScreenUpdating = F...
Sid29
1

votes
3

answer
39

views

reshape a dataframe using pandas

I have a .csv file like this ID FirstName LastName Age FirstName LastName Age 1 Sid Than 21 Sidd Thang 26 2 Art Mari 21 Arth Mariap 28 When I read this inside python using pandas the column names automatically changes to FirstName_y LastNam...
Sid29
1

votes
1

answer
32

views

Python- defining a function that takes data-frame as an Input

I am trying to create a function that alters the datatypes of the columns. def ChangeDatatypes(df): df['Name'] = df['Name'] .astype('category') df['Character'] = df['Character'] .astype('category') cat_columns = df.select_dtypes(['category']).columns df[cat_columns] = df[cat_columns].apply(lambda x:...
Sid29
2

votes
2

answer
31

views

Compare two date columns that has texts on it

I have a dataframe like this, datecol1 datecol2 2005-02-22 EmployeeNotFound 2010-02-21 2010-02-22 EmployeeNotFound EmployeeNotFound EmployeeNotFound 2010-02-22 Both the columns have a dtype of Object. I want to compare these two columns and get the maximum date o...
Sid29
1

votes
2

answer
92

views

Pandas - Check if any column is date time and change it to date format string (yyyy-mm-dd)

How can we use pandas to check, if any column is a datetime datatype column and then convert only that column to a date format string (yyyy-mm-dd) datatype column. I have multiple columns that are datetime. So, I cannot go by column name and change it. But would rather prefer a way that checks and t...
Sid29
1

votes
1

answer
118

views

Pandas change column datatypes on all columns

I have more than 100 columns in my pandas data frame. I want to dynamically parse each column and change the data type of the column. For example, if the column name has date on it, then change the column to date type, else convert the column to str. Something like this works for me, df['Sasd Date'...
Sid29
1

votes
2

answer
30

views

Pandas - Compare Columns and return False when they are not matching and also when one of the column says “Column not found”

I am comparing two columns using Pandas. Pre_Out_df[res_name] = Pre_Out_df[plain_col] == Pre_Out_df[b_col] The above code returns false. But I want the code to return false when any one of the value between Plain_col and b_col says'Column Not Found'. I want to return false on the last case in the d...
Sid29
1

votes
1

answer
29

views

Pandas - Get the corresponding value from dictionary while comparing

I have a dictionary (not a dataframe) like this, Emp ID dictvalue 1 True 2 False And I have a dataframe like this, Emp ID ColA ColB 1 A B 2 A A I am doing a compare between ColA and ColB temp_result_df[res_col_name] = (temp_result_df[primar...
Sid29

View additional