Hours difference between two dates & exclude bank holidays and weekends
Hi there, I am trying to find a simple way to calculate the hours difference between two dates. But I need to exclude bank holidays and weekends but also the calculation can only sum the hours in-between specified working hours(which is 9am to 6pm) . If something comes through outside of these working hours the sum would only start the next day at the working hour start time. above are the refrence dates Previous date is the start date of a task and Action date is the end date of the task. I WANT TO DO THIS IN POWER QUERY ONLY i will be very thankful, if u help me out with this logic i need to do it on a urgent basics (ASAP) link of the Pbix file is given below https://1drv.ms/u/s!AsQOj7VQoTfbkUrTCIwpZCrE-968?e=aNZ97N
Dynamic Measure Required with two slicer
Hi Folks, I'm trying to develop the dashboard by comparing two months and their uses of two slicers. I prepared the dashboard using Excel Formulas. please help to develop Power-BI As the Raw data is attached Excel is available. please refer it Google Drive Link for Excel File Please feel free to contact for more info if required. RAW Data Period Account Value X Value Y Value Z Fix Tata 4 3 5 Fix Birla 6 7 8 Fix Adani 7 8 9 Fix RIL 8 9 10 Fix Tata 9 10 11 Fix Birla 10 11 12 Jan Adani 11 12 13 Jan RIL 12 13 14 Jan Tata 13 14 15 Jan Birla 14 15 16 Jan Adani 15 16 17 Feb RIL 16 17 18 Feb Tata 17 18 19 Feb Birla 18 19 20 Feb Adani 19 20 21 Feb RIL 20 21 22 March Tata 21 22 23 March Birla 22 23 24 March Adani 23 24 25 March RIL 24 25 26 March Tata 25 26 27 March Birla 26 27 28 March Adani 27 28 29 April RIL 28 29 30 April Tata 29 30 31 April Birla 30 31 32 April Adani 31 32 33 April RIL 32 33 34 April Tata 33 34 35 Regards, MOHITData Source is empty when I use Blank query with API
Hi All, My API needs to iterate multiple times, so I updated the M code and got the output. However, when I try to refresh the dashboard in Power BI report server, the data source isn't enabled, and when I check the data source settings in Power BI desktop, it's empty. How can I schedule the dashboard? Below code is used in blank query. ----------------------------------------------------------------------------------------------------------- let // Define the function to fetch data from the API GetData = (url) => let // Make the API request Source = Json.Document(Web.Contents(url)), items = Source[items], hasMore = Source[hasMore], nextUrl = if hasMore then Source[links]{3}[href] else null, itemsTable = Table.FromRecords(items) in [Items = itemsTable, HasMore = hasMore, NextUrl = nextUrl], // Define the function to fetch data recursively FetchDataRecursive = (url, accumulatedData) => let // Fetch the next set of data nextData = GetData(url), // Update loop variables newData = Table.Combine({accumulatedData, nextData[Items]}), hasNextPage = nextData[HasMore], nextUrl = nextData[NextUrl] in if hasNextPage <> false and nextUrl <> null then @FetchDataRecursive(nextUrl, newData) else newData, // Initial call to fetch data apiUrl = "https://au.................................", initialData = GetData(apiUrl), finalData = FetchDataRecursive(initialData[NextUrl], initialData[Items]) in finalData --------------------------------------------------------------------------------------------------------------------- Datasouce is empty
Converting Data Per Minute to Hourly in Power BI
I need help with data transformation in Power BI. The data I have is stored in the data source with a minute-level granularity, but I want to display the data in the report on an hourly basis. How can I perform this transformation using Power Query Editor to show the data in hourly intervals with the appropriate aggregation (e.g., average)? Thank you for your assistance!
Creating Burndown chart using scheduled completion dates and actual completed jobs
Hello, (Look at reference image above) I am creating a dashboard to record current and completed jobs. The data is getting pulled in automatically with 2 date columns for current jobs and completed jobs. The 2 dates include: actual completion date and scheduled completion date. So the current jobs come with 2 date columns and the completed jobs come with 2 date columns. I am trying to create a bunrdown chart that displays the amount of scheduled completed jobs and the actual completed jobs per month (see example picture above). So in july, you have two columns, Red column(SCD: Scheduled completed jobs) and blue column(ACD: Actual completed jobs). If 15 jobs are scheduled to be completed in july(red) and 10 jobs are actually completed in july(blue), then those should display in the chart. Below, you can see a very rudementry chart with a small data set. Dark blue being SCD and light blue being ACD. I did this in a method that does not work in all instances so I am trying to figure out a knew method. I am very new to power BI but I ended up using Inventory Item ID (unique values) as a way to count the amount of jobs in that month. Below you can see what columns I used and where I used them in the visualizations tab: If you look back to the first image, you can see the line starting at x=START, y=100. Lets just say that we are starting with 100 jobs at the start of the year and we want to know by december, how many we schedule to get done. We want to continueously subtract from the intial amount of jobs, so by every month, you know how much you have left. To do that, I was thinking I could subract the count of completed jobs from the month before, and then once we get to the months that havent happened yet, you subract the shceduled completed jobs from the month before. You can see in september( lets just say that its september next month), we dont have any actually completed jobs because thats in the future. So there are no completed jobs, so you would use the scheduled ones to know when you'll eventually get to 0 jobs left. Again I am new to BI so I may be thinking this way wrong but any help to this would be great! Thanks for taking the time!!!
Power Query Refreshing
Hey everyone! I'm new to the Power BI Community and I'm running into an unexpected issue. I'm using Power BI Report Server and connected to and importing a table from Oracle DB. The problem is, everytime I edit a column or make any tranformation within the data(e.g Replace values), it refreshes the data from the data source, reading the complete database each time and reapplying the filters. As you can imagine, with millions of rows this is causing immense wastage of time and is a serious set back for us. Is this supposed to happen? Or could I be doing something wrong?Transpose or Upivot?
Hello everyone, I am hoping someone can help me with transposing or unpivoting some columns. I am basically trying to turn this: Into this in Power Query Editor: I've tried unpivoting a few different ways, but no luck with attaching the reponse and the commentary to the same Question and ID. Any help would be appreciated. Thanks!
