PowerrrBrrr Tracker

Re: Gets "code":"Unauthorized" error when accessing one lake in microsoft fabric

PowerrrBrrr — Thu, 25 Jan 2024 10:18:18 GMT

sure if you provision KQL database in Fabric, you get the below details and the URL of One Lake which i would like to access

Gets "code":"Unauthorized" error when accessing one lake in microsoft fabric

PowerrrBrrr — Mon, 20 May 2024 21:06:50 GMT

I created a KQL database which has event hub as the source and I can see the data in the KQl table I created. However , after activating the One lake path and I try to open it in new window. I get the below error:

{"error":{"code":"Unauthorized","message":"Authentication Failed with Bearer token is not present in the request"}}

What wrong am I doing. What permission do I need to check. couldnt find in Fabric documentation anything on generating token

How to get the latest date value from the column

PowerrrBrrr — Thu, 29 Sep 2022 19:04:57 GMT

I have following table and I need the repeated row with latest date. I need only those values which was signed latest

Name	User	Date Checked Out
ABC	OOO	2017-12-14T11:47:00
ABC	XXX	2017-12-14T11:57:00
ABC	HHH	2017-12-14T11:17:00
DEF	VAA	2017-12-17T11:47:00
DEF	KAS	2017-12-17T11:00:00

Here I need result like below:

ABC		XXX	2017-12-14T11:57:00
DEF		VAA	2017-12-17T11:47:00

Can someone please help?

Re: How to get the maximum occurence for a category

PowerrrBrrr — Fri, 01 Jul 2022 13:33:48 GMT

What is @num passes for. I tried and i get error as Column 'Subject' cannot be found or may not be used in this expression

How to get the maximum occurence for a category

PowerrrBrrr — Fri, 01 Jul 2022 10:04:23 GMT

I have table like this

Subject	Status
Math	Pass
Math	Pass
English	Fail
English	Pass
Math	Pass
Hindi	Fail
Hindi	Fail
Math	Fail

I need to list the subject which has maximum "Pass". i.e. here Math should appear in my Visual as it appeard 3 times

Get count for a particular category

PowerrrBrrr — Fri, 01 Jul 2022 03:52:42 GMT

Hi, I am working on Azure policy dashboard and I need to show number of non-complaint resources per subscription per policy assignment per category.

here Category, Policy Assignment and Complaint status(value: Complaint, Non Complaint) column is in 1st table , Subscription column is in 2nd table and Resource name is in 3rd table. There is a relationship which has been defined between 3 tables. Would like to know How can I show Number of Non-complaint resource in a visual per subscription, per assignment per category. Visual should be static i.e. shows all subscription and count of non complaint resources under one visual

How to rename Legend values for a Visual

PowerrrBrrr — Wed, 19 Jan 2022 12:59:36 GMT

I have a line chart and have a column which is added as Legend. I would like to rename the Legend values something more meaningful in the visual. How can I do it? For example when I add my column ID in Legend Field in Line chart. I get

ID
UU272652
22577IIU

I would like to replace these two values of ID as
ID
Rate by Speed
Rate By Distance.

How can I do it?

When to use Dataflow vs Dataset?

PowerrrBrrr — Tue, 14 Dec 2021 18:23:27 GMT

Hi, So our organization has been using Postgre connector as source for building reports and have been working without any issue. Recently, we had issues with some of the users where data size has been large and have been using good amount of power queries to do the transformation and it takes lot of time to load data each time in power Bi service. We see dataflows do provide an answer to our problem and we are able to handle most of our use case with dataflows. But then we have the same thing with Power Bi datasets where user can create a dataset and use it to build the report and have different timestamp to refresh dataset and Power Bi report as we do now with dataflow. So my question is why dataflow and on what scenario will dataflow will actually be useful with Power Bi rather than power bi datasets? I did some research and agree tha with dataflows the data is hosted over a Azure instance and that is well integrated with PowerBi then data being hosted some where else. but is that the only reason why we should go for dataflow?
Would really like this to be answered by some expert who has been using both services

Re: Filter a value in a column by passing values from other table

PowerrrBrrr — Sat, 27 Nov 2021 15:16:13 GMT

nope , there is a reason, it has been converted into direct query mode

Re: Filter a value in a column by passing values from other table

PowerrrBrrr — Sat, 27 Nov 2021 14:15:16 GMT

hi negi007 .. I forgot to add one small detail here, my table B is a direct Query table and table A is coming from Import. I guess I wont be able to create a new table from Table B. Is there any other way i can approach this?

Filter a value in a column by passing values from other table

PowerrrBrrr — Fri, 26 Nov 2021 13:32:29 GMT

I have two tables with one to many relationship. Table A has Name and Ids and table B has Ids, timestamp and value.
These values are latitude and longitude. How can I filter the values based on Name in table A and plot it on map visual?

Table A

Id	Name
1	A_LAT
2	B_LAT
3	A_LONG
4	B_LONG

Table B:

ID	timestamp	Value
1	2021-01-03	22.8
2	2021-01-03	21.8
1	2021-01-05	12.8
2	2021-01-05	22.8
3	2021-01-03	32.8
3	2021-01-05	52.8
4	2021-01-03	82.8
4	2021-01-05	72.8

So as per above, I have to plot (A_LAT, A_LONG) and (B_LAT, B_LONG) in the map where LAT and LONG are Lattitude and longitude and as per above table we have two points for A and two for B (based on time stamp)
(22.8, 32.8) (12.8, 52.8 ) for A and (21.8, 82.8) (22.8, 72.8 )

Can I write a measure for this??

Re: What does this power query does?

PowerrrBrrr — Thu, 28 Oct 2021 10:06:17 GMT

I dont know what to say to you.. I know that there is merging happening, but as I mentioned in the question, i didnt get how and why the filtering is happening on max columns once for null and then without null and why and how this is getting merged.

What does this power query does?

PowerrrBrrr — Thu, 28 Oct 2021 09:01:34 GMT

I have a report and I am trying to understand the query written in there. This is one of the table which has been manually created and have columns "max" and "min" along with others. I dont understand what the below queries are doing. in one step there is a filtering with max as no null and then max as null and then there is merging but there is no 2nd table defined.

What is happening here?? Can someone explain?

#"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([max] <> null)),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([max] = null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"max", "min"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"source_ext_id"}, #"Filtered Rows2", {"source_ext_id"}, "max", JoinKind.LeftOuter),
#"Expanded max" = Table.ExpandTableColumn(#"Merged Queries", "max", {"max", "min"}, {"max", "min"}),

Re: Get the category with maximum value

PowerrrBrrr — Thu, 28 Oct 2021 08:07:56 GMT

Its there with the solution Jose provided, in your case its "

_maxValue

" both give the same result and same error

Re: Get the category with maximum value

PowerrrBrrr — Thu, 28 Oct 2021 07:48:32 GMT

I cant provide the pbix as it contains sensitive data.. but its strange if I return the maxpressure value I get it as Blank. but If I try to return it with an IF condition I get error as multiple value supplied. Is it because there are multiple Blank values??

Re: Get the category with maximum value

PowerrrBrrr — Thu, 28 Oct 2021 07:29:52 GMT

I am getting Blank even when the value is not zero or blank. ie. if I do

RETURN IF(ISBLANK(MaxPressureonLatestDate)||MaxPressureonLatestDate=0,BLANK(),MachineMaxPressureonLatestDate)

I get error as multiple value supplied but when I just Return MaxPressureonLatestDate,. I get value as Blank

Re: Get the category with maximum value

PowerrrBrrr — Wed, 27 Oct 2021 10:16:10 GMT

Jos_Woolley Modified my question and table as requested. Please check now. You solution works when there is a value in the "Value" field but gives error when the value is blank (which can be a case some day)

Re: Filter not working as expected

PowerrrBrrr — Tue, 26 Oct 2021 08:51:30 GMT

Yes that works, but would like to know why this does not work with Filter on Pages??I am here just cleaning the data and removing blank

Re: Filter not working as expected

PowerrrBrrr — Tue, 26 Oct 2021 07:22:27 GMT

Hi v-henryk-mstf

I did your way and its still not working..there is no error. please see the below screen shot. I have not selected anything, still its takes the first condition in filter

Re: Get the category with maximum value

PowerrrBrrr — Mon, 25 Oct 2021 11:34:38 GMT

Jos_Woolley Your solution works fine but fails when the pressure is blank on latest day. Like if the latest there is no pressure recorded on any machine on latest day, it should give me blank or not recorded instead of machine name but what I get is error as "A table of Multiple values were supplied while it was expected a single value". how can i handle this