Search results

Personally, I'd recommend doing calculation in M code (power query) stage. About 90% of calculated columns are more efficient in PQ when compared to DAX calculated columns (especially when it's within single table context). Also, I'm not a fan of mixing numeric and text values in single column...

Without knowing your data structure and how your DAX measures are constructed. Bit hard to help you. DAX is highly contextual in nature and how it's evaluated will depend on few things.

Personally, I'd recommend adding date/time stamp at form data entry stage. It's not ideal to add stamp when you are consuming (querying) data. If that isn't possible, best bet is to use self referencing query and build your query in stages. See tutorial below for how to construct self...

FYI - If it is <table> element within html that you wanted to extract. You don't need special convoluted function. The function you posted above is to apply transformation(s) using single function to entire table or to specific columns within PowerQuery. Not directly related to parsing out table...

This has nothing to do with JSON. Data is actually in html content of the page. However, since page is heavily nested in div and other style container tags, it is difficult to find/navigate to the desired data from elements table. This isn't a easy site to scrape data using PowerQuery due to...

Dictionary can hold in it's item any object, array, data etc. Key must be data type (i.e. String, number etc). So by adding range object (i.e. more than 1 cell) as item. You will end up needing additional process to read content of the range. Defeating purpose of this dictionary object. To...

VBA IDE can't display Arabic characters. It's limitation of the IDE. I.E. No Debug.Print or MsgBox. Although, if you want to translate Arabic to English. I'd imagine that you would have Arabic as key and English as Item. Ex: Where B2:B4 hold Arabic and C2:C4 hold corresponding English...

Assuming latter. I'd normally do it using custom M function. But that can be bit hard to debug. So left it as simple transformation steps. Add Index column to the table. Add custom column with following. = if [Account] = null then Text.Start(Text.From(#"Changed Type"[Account]{[Index]-1}),1)...

I still don't get your logic... how do you count/calculate from # of days, devices that had same status? Here's sample that shows # of off status by device in a given day.

In Power Query, you cannot reference previous row of the same column. Does your actual data have aggr_level followed by number? If so, it's simple extraction logic. If not, you will need bit more logic.

But how are you proposing that you connect that table to your data? Can't really help you without understanding your logic/relationship structure.

How is your Numbers 1 to 31 related to your data? I see no column that would relate the two. Are you simply counting day 1 as first date found in record? As well, in general you can't display output data format in data visual from data model. Unless you transform your table to show each day as...

Simply use [Red]0.00;-0.00 Or are you saying that Jun value is < Jul value, format Jul? If that is the case, you need conditional format. However, it isn't clear to me exactly what you need.

It would help if you upload sample data. Is timeline a string value or is it actual datetime value? How is your slicer connected to the data? Without knowing your data structure it is a bit difficult to help.

Are you sure that your sample is in exact format that your original is in? It's very similar in structure to JSON, but does not conform to JSON standard. As @p45cal mentioned, it's missing opening and closing curly brackets. If your file is actually missing curly brackets. Here's what you can...

Sure. If it works for you. Go with it. Part 1 isn't about memory leak, but about data integrity. Excel and ADO isn't built for ACID, you always run some risk of inconsistency / conflict when working with open workbook. As there is no native data lock mechanism. But if you are only reading data...

I'd recommend doing some test of your own... 1. When another user has workbook open and actively working on it. At the same time you are querying workbook. 2. Where? I just ran multiple successive query on open workbook and monitored memory usage. 3. Huh? PQ is ETL tool not just displaying data.

That and also, since data queried is going to be from last saved data and not necessarily latest live data on open workbook. When querying closed workbook, you know it's the latest data stored in the workbook. ADODB has memory leak on querying open workbook. This has never been fixed to my...

As far as I'm aware, not possible. It may be possible using query on underlying xml and parsing query code, but I doubt it. There isn't way for B file to parse M code in query.

You individually unpivoted date column. Instead you should select all date columns and unpivot at once. Ex: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Region", "Branch Category"}, "Attribute", "Value")...

Not Power BI. Power Query (also called Get & Transform). It's in the data tab. I assumed you had it, since you posted in Power tools section of the forum.

First, I'd double check your data. Some Nationality is misspelled/wrong. Ex: Bharaini should be Bahraini, Saudi Arabian should be Saudi. etc. At any rate, you would start off with creating translation table for Country and what corresponding nationality would be. Country People Algeria...

Unpivot columns (Power Query) (microsoft.com)

So it's simply matter of creating column. Something like... = if [Milestone 1 Actual Date] = "" then [Milestone 1 Forecast Date] else [Milestone 1 Actual Date] Then for CF, I'd recommend that you add another column to use as condition column. = if [Milestone 1 Actual Date] <> "" then 0 else if...

Without sample workbook with data set and mockup of desired result, bit hard to help.