HI,
I have a excel file in which I pull data from SQL into a Table by name of PURCHASEDATA.. This data keeps changing and I do a refresh every day..
From this PURCHASEDATA using power query, I generate four more tables containing specific smaller subsets of data.
One of the resultant table <NEWUSER> that I generate from Power Query relates to identifying a new user ... This data I use to generate a Chart to display new Unique User week on week.
While I am not sure if this can be achieved in the query itself, what I generally do is to pull a set of columns through Power Query out of which one of them is Requestor data column. Towards the end of the NEWUSER, I have manually added a countif formula to tell if the user is a new user or an existing one. The formula works fine as is.. I have depicted in the first table below the formula column that I have added. A value of ZERO from the formula would indicate that the user in that line record is a New user.. All works fine until I refresh the data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row#[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=COUNTIF($F$1:F4,[@Requestor])[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]=COUNTIF($F$1:F5,[@Requestor])[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]=COUNTIF($F$1:F6,[@Requestor])[/TD]
[/TR]
</tbody>[/TABLE]
Now the table below is what happens when I do a refresh of the SQL data and subsequently refresh the Query... I still get the basic data correctly. however the count formula does not auto expend and cover all the rows.. The formula on Row 10 should ideally be on Row 8... I have to copy the formula manually again from row 8 till end of the table
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row#[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=COUNTIF($F$1:F4,[@Requestor])[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]=COUNTIF($F$1:F5,[@Requestor])[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]=COUNTIF($F$1:F6,[@Requestor])[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]=COUNTIF($F$1:F7,[@Requestor])[/TD]
[/TR]
</tbody>[/TABLE]
is there a way to replicate the formula in Power Query? Or Can I generate the unique count with a different formula which works out even if data is refreshed
I have a excel file in which I pull data from SQL into a Table by name of PURCHASEDATA.. This data keeps changing and I do a refresh every day..
From this PURCHASEDATA using power query, I generate four more tables containing specific smaller subsets of data.
One of the resultant table <NEWUSER> that I generate from Power Query relates to identifying a new user ... This data I use to generate a Chart to display new Unique User week on week.
While I am not sure if this can be achieved in the query itself, what I generally do is to pull a set of columns through Power Query out of which one of them is Requestor data column. Towards the end of the NEWUSER, I have manually added a countif formula to tell if the user is a new user or an existing one. The formula works fine as is.. I have depicted in the first table below the formula column that I have added. A value of ZERO from the formula would indicate that the user in that line record is a New user.. All works fine until I refresh the data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row#[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=COUNTIF($F$1:F4,[@Requestor])[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]=COUNTIF($F$1:F5,[@Requestor])[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]=COUNTIF($F$1:F6,[@Requestor])[/TD]
[/TR]
</tbody>[/TABLE]
Now the table below is what happens when I do a refresh of the SQL data and subsequently refresh the Query... I still get the basic data correctly. however the count formula does not auto expend and cover all the rows.. The formula on Row 10 should ideally be on Row 8... I have to copy the formula manually again from row 8 till end of the table
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row#[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=COUNTIF($F$1:F4,[@Requestor])[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]=COUNTIF($F$1:F5,[@Requestor])[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]=COUNTIF($F$1:F6,[@Requestor])[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]=COUNTIF($F$1:F7,[@Requestor])[/TD]
[/TR]
</tbody>[/TABLE]
is there a way to replicate the formula in Power Query? Or Can I generate the unique count with a different formula which works out even if data is refreshed
