[PowerQuery] Decompress .zip Files
Recently, I have noticed that there was a lot of content talking about gzip decompress with Power Query, but I haven't seen someone talking about decompressing .zip files. The truth is that gzip is a most common file extension for data with the Azure suite. We can handle gzip with Azure Data Lake and Azure Data Factory. Anyway, in this article we will check how to decompress a ZIP file to explore your files inside, like a Windows folder in Power Query, in a very similar way as the gzip is done. If you haven't seen how gzip works, you can check this post about it.
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, MOHIT
Parent and child calculation
i have a parent and child table, i wanted to calculate the values with respect to their root parents like a family tree Parent Child Value Site1 Site2 10 Site2 Site3 20 Site3 Site4 15 The output should be, Parent Child Value Output Site1 Site2 10 10 Site2 Site3 20 30 Site3 Site4 15 45 Thanks in advance
I need help to create a new table from another one
I don't know if this is tricky or is just me, but here goes nothing. I have one table with a group of columns that are months and another group of columns that are also months, each group have a different context in its values; one group is a planified budget, the other group is the real disbursement. Something like this: id title jan_planified_amount feb_planified_amount (…) jan_real_amount feb_real_amount (...) 1 example_1 $500,00 $0,00 (…) $0,00 $500,00 (…) So I tried creating two new tables, one for each group and unpivot the columns, keeping the ids, the months and the amount. Then I create a relation many to many and create a new table with only ids, but when I tried putting the values in a chart it does not work. So I need another way to relate these tables or create only one table with both values like this: id title month planified amount real amount 1 example_1 jan $500,00 $0,00 1 example_1 feb $0,00 $500,00 1 example_1 mar $1.000,00 $1.000,00 1 example_1 apr $0,00 $0,00 1 example_1 may $0,00 $0,00 1 example_1 jun $0,00 $0,00 1 example_1 jul $0,00 $0,00 1 example_1 aug $0,00 $0,00 1 example_1 sep $1.000,00 $0,00 1 example_1 oct $0,00 $0,00 1 example_1 nov $0,00 $0,00 1 example_1 dic $1.000,00 $2.000,00 Because I need a chart like this: Been the bars the planified amount and the line the real amount. I don't know if this is the best way to achieve this but is the only thing I cant think of. I need a little help.Power BI Dev Camp 25 March, 2021 - Session 8 - Deep Dive into M Programming
Do you every dream of becoming the Heavy-weight Power Query Champion of the World? Join our Power BI Dev Camp session on March 25th for a fast-paced primer on the fundamentals of the M programming language. The goal of this deep dive session is to give campers a stronger foundation for working directly with M code in the Advanced Editor when designing queries for datasets in Power BI Desktop or when designing queries for dataflows in the browser.Lookup Table in Power Query
I have prepared an article about a transitive closure in Power Query (coming in 2 weeks). I wanted to publish the article already, but I decided to wait a little bit and write another one about a performance boost I’ve used in the code. It is a lookup table in Power Query.To Transpose or Unpivot? What you need to know about table structuring in Power Query (2)
Only 6% of Analytics Reporting in organizations source their data from standard databases. Oh no, I made up that number. I have no idea what the numbers are. But I am certain that the majority of self-service BI & data analytics users must deal with a lot of crappy data. One of the big issues Power Query users must also deal with is Table Structuring. When should you use “Transpose” and when should you use “Unpivot”?Solving Real Life Problems with Recursive Functions in PowerQuery
Every respected computer scientist has heard about recursive functions (more details on Wiki: https://en.wikipedia.org/wiki/Recursion). There are many algorithms which are mentioned as example of such functions, i.e. Fibonacci sequence as the simplest example: Fib(0) = 0 Fib(1) = 1 For all integers n > 1, Fib(n) = Fib(n-1) + Fib(n-2) How do we rewrite this pseudo code into a function in PowerQuery? let fnFibonacci = (value as number) as number => if value = 0 then 0 else if value = 1 then 1 else @fnFibonacci(value - 1) + @fnFibonacci(value - 2), Source = List.Transform({0..10}, fnFibonacci) in Source The most important part is the use of @ before we call the recursive function. We tell the PowerQuery to reference its own name from inside itself. Let’s test our function: Well, that is great but not very useful in real life. I have been searching for long time for a scenario which can use recursive functions in a meaningful way. And I have found one! In PowerQuery, you can select or rename many columns in one step. Moreover, you can change their data types at once. But repetitive modifications of a table are not so easy anymore. In my ETL process written in PowerQuery, I wanted to split every datetime column in a fact table into 2 separate columns: date and time. It has at least 2 big advantages. First of all, date and time values become a foreign key for my date and time dimensions; second, the size of my dataset will decrease dramatically. This means that instead of many unique values I’ll get only 365 unique values a year for date dimension and 86 400 unique values for time dimension with the precision of one second. What are my options? I can create a new custom column of date datatype for every datetime column and another custom column for time. Yes, I can, but I do not like doing a repetitive work. Let’s try to automate it. Firstly, we need some test data. // create a table with some datetime columns Source = Table.FromRecords( { [OrderID = 1, CustomerID = 1, OrderPlaced_dt = #datetime(2019,6,16,10,0,0), OrderPaid_dt = #datetime(2019,6,16,10,5,0), OrderShipped_dt = #datetime(2019,6,16,11,0,0), Price = 100.0], [OrderID = 2, CustomerID = 1, OrderPlaced_dt = #datetime(2019,6,16,12,12,12), OrderPaid_dt = #datetime(2019,6,16,13,13,13), OrderShipped_dt = null, Price = 200.0] }, type table[OrderID = Int64.Type, CustomerID = Int64.Type, OrderPlaced_dt = DateTime.Type, OrderPaid_dt = DateTime.Type, OrderShipped_dt = DateTime.Type, Price = Decimal.Type] ), Next, let’s create a function, which has 2 parameters - a source table and a name of a datetime column. This function does the same what you would do in the UI. It creates 2 new columns and removes the original one. // split a datetime column into 2 columns: date and time fnSplitDateTimeColumn = (parTbl as table, parColumnName as text) as table => let // add a new column with date transformDateExpression = Expression.Evaluate("each Date.From([" & parColumnName & "])", #shared), addDateColumn = Table.AddColumn(parTbl, parColumnName & "_date", transformDateExpression, type date), // add a new column with time transformTimeExpression = Expression.Evaluate("each try #time(Time.Hour([" & parColumnName & "]),Time.Minute([" & parColumnName & "]),Time.Second([" & parColumnName & "])) otherwise null", #shared), addTimeColumn = Table.AddColumn(addDateColumn, parColumnName & "_time", transformTimeExpression, type time), // remove datetime column removeDateTimeColumn = Table.RemoveColumns(addTimeColumn, parColumnName) in removeDateTimeColumn, And finally, in the last step we create another function which is recursive. This function has also 2 parameters - a source table and a list of all datetime column names which we haven’t transformed yet. The function takes the first item from the list (a datetime column name), splits this datetime column into 2 columns, and calls itself recursively. The recursive call uses the last step as the new source table and a list of all datetime column names except the first one (which is already processed). Once the list is empty, the recursion terminates. The recursion function returns a modified source table – instead of one datetime columns there are now 2 columns. // recursive function which splits all datetime columns into date and time columns // parTbl is a source table to modify, parColumnNameList is a list of columns to split fnSplitAllDateTimeColumns = (parTbl as table, parColumnNameList as list) as table => // if parColumNameList is empty, terminate the recursion if List.IsEmpty(parColumnNameList) then parTbl else let // get one column name to process currentColumnName = List.First(parColumnNameList), // remove first item from the parColumnNameList nextColumNameList = List.RemoveFirstN(parColumnNameList, 1), // split current column splitOneColumnTable = fnSplitDateTimeColumn(parTbl, currentColumnName), // call itself recursively with a new created table and a shortend column name list nextIterationTable = @fnSplitAllDateTimeColumns(splitOneColumnTable, nextColumNameList) in nextIterationTable, And everything together: let // create a table with some datetime columns Source = Table.FromRecords( { [OrderID = 1, CustomerID = 1, OrderPlaced_dt = #datetime(2019,6,16,10,0,0), OrderPaid_dt = #datetime(2019,6,16,10,5,0), OrderShipped_dt = #datetime(2019,6,16,11,0,0), Price = 100.0], [OrderID = 2, CustomerID = 1, OrderPlaced_dt = #datetime(2019,6,16,12,12,12), OrderPaid_dt = #datetime(2019,6,16,13,13,13), OrderShipped_dt = null, Price = 200.0] }, type table[OrderID = Int64.Type, CustomerID = Int64.Type, OrderPlaced_dt = DateTime.Type, OrderPaid_dt = DateTime.Type, OrderShipped_dt = DateTime.Type, Price = Decimal.Type] ), // split a datetime column into 2 columns: date and time fnSplitDateTimeColumn = (parTbl as table, parColumnName as text) as table => let // add a new column with date transformDateExpression = Expression.Evaluate("each Date.From([" & parColumnName & "])", #shared), addDateColumn = Table.AddColumn(parTbl, parColumnName & "_date", transformDateExpression, type date), // add a new column with time transformTimeExpression = Expression.Evaluate("each try #time(Time.Hour([" & parColumnName & "]),Time.Minute([" & parColumnName & "]),Time.Second([" & parColumnName & "])) otherwise null", #shared), addTimeColumn = Table.AddColumn(addDateColumn, parColumnName & "_time", transformTimeExpression, type time), // remove datetime column removeDateTimeColumn = Table.RemoveColumns(addTimeColumn, parColumnName) in removeDateTimeColumn, // recursive function which splits all datetime columns into date and time columns // parTbl is a source table to modify, parColumnNameList is a list of columns to split fnSplitAllDateTimeColumns = (parTbl as table, parColumnNameList as list) as table => // if parColumNameList is empty, terminate the recursion if List.IsEmpty(parColumnNameList) then parTbl else let // get one column name to process currentColumnName = List.First(parColumnNameList), // remove first item from the parColumnNameList nextColumNameList = List.RemoveFirstN(parColumnNameList, 1), // split current column splitOneColumnTable = fnSplitDateTimeColumn(parTbl, currentColumnName), // call itself recursively with a new created table and a shortend column name list nextIterationTable = @fnSplitAllDateTimeColumns(splitOneColumnTable, nextColumNameList) in nextIterationTable, // get all columns having the datatype datetime DateTimeColumnNames = Table.ColumnsOfType(Source, {type datetime}), // split all datetime columns SplitAllDateTimeColumns = fnSplitAllDateTimeColumns(Source, DateTimeColumnNames) in SplitAllDateTimeColumns And the result: Is it a lot of code for such a simple task which you can manage in PowerQuery Editor with shiny UI? Yes and no. If you have many fact tables with a lot of datetime columns and you do an ETL, you’ll be very happy having the opportunity to automate that. But if you have just one table with 2 datetime columns, it is an overkill. Do you know any other pragmatic use case for recursive functions in PowerQuery? Please, let me know down in the comments 😊
