How to write a script or any PQ or in Excel to download the zip files from a Webpage
Dear Experts, Greetings! https://www.etsi.org/deliver/etsi_ts/138300_138399/138306/ Could you please help me on how to download the pdf.zip files from above for all the versions? Using a single command in Excel or PQ-option. Thanks in Advance, Br, Anupam
Building relationships in data model to leverage power pivot - circumventing unique key ID issue
When building relationships between two data tables in the data model of excel, you require a unique ID / key to relate the two datasets. This makes sense but I find it has an issue: It requires you to reduce the entirety of your dataset down to unique ID's in one of your tables so that it can do the 'one to many' relationship build. If you don't have unique ID's / key, the relationship between two tables often doesn't work. Yes you can use an intermediary table with unique ID's to link the two complete tables, but when using that intermediary table I find you are limited in what additional data rows you can bring into the combined power pivot table to provide context to the relationship. Once you start bringing in contextual rows from the separate tables, there ends up being a lot of repeated values for any numerical figure from the other table, which you are trying to aggregate. An example below of the power pivot table result (data from table 1 in blue, from table 2 in green) Product ID (Unique Key) Service Line Sales Amount COGS 169AKY T Shirts $1,000 $7,500 169AKY Sweaters $2,000 $7,500 169AKY Socks $3,000 $7,500 169AKY Turtle necks $4,000 $7,500 169AKY Pants $5,000 $7,500 COGS from table 2 also has the data broken out by Service Line (T-shirts, Sweaters, etc.), but will only show an aggregate COGS value for all service lines for each unique product ID, instead of breaking it out by Service Line. This is because the Service Line column is being inserted into the power pivot table from table 1, it breaks out sales by Service Line. If you insert the Service Line data column into the power pivot from table 2, COGS would be broken out by Service Line, and only an aggregate for sales would be produced. Ideally the power pivot would display sales AND COGS broken out by Service Line (and Product ID). Is there a best practice for building relationships (unique ID's) between data tables: Without reducing the richness / variety of columns and data in your original tables (when creating the unique ID)? Maybe 'group by' function in power query to obtain unique ID's before building the relationship? With retaining the ability to slice and dice the data by different characteristics (assuming they are present in both source tables) and have that detail presented in the power pivot. Thanks for the help! The humble Apprentice
Filter Function or any Logic
Dear Experts, I have a data like below , Column "H" has rnti's , Column "F" has sfn which can range from 0~1023( and once 1023 it shall start again from 0) slots that can range from 0~9, each sfn(say 252) has slots(0~9), Column "J" is Sn( Sequence number) , In Output , I want like this, example for the rnti 384, column "B" = count of spdu-1 for the rnti=384, and so on How the spdu-1 counted? (spdu-x's are counted per rnti & per-rlcCtrlAckSn_ ,) as below for example for rnti==384, Thanks in Advance, I posted this earlier also , not sure why post got deleted somehowHow to highlight duplicated sequential rows
I have a data like below sfn can range from 0~1023, and then again start with 0, slots can range from 0~19 I want to highlight the rows like below:- so, 331.12 is repeated in ROWS(4,5) so highlighted in Yellow, 331.13 is repeated in ROWS(6,7) so highlighted in Yellow and so on. if any Conditional formatting or anyother logic, by which we can highlight these data , would be helpful. Worksheet attached. Br, Anupam
Power Query - Large Data Set Question
Hello - I combined a handful of .xlsx files and there's about 5 million rows total. I'm trying to identify duplicate "project IDs" (the long strings below in rows 22-29), basically what this filter is showing is all of the project IDs that have 3, 4, 5, 6, etc. duplicates all the way through 14. That's exactly what I'm looking for, there's about 180k project IDs I was able to get. The problem is the project IDs that have 1 duplicate, which is the number 2 in this filter because I did a group by in power query by the project ID and it counts the number of matching rows it has, so 2 means it has 1 duplicate. When I filter on this it runs over the 1 million excel row limit, I was wondering if anybody had an idea of how I could get around this problem?Calculation formula for repetition entries
Dear Experts, I have a data like below in Home-Worksheet :- In Output, I want to calculate the count of spdu-1, spdu-2 per rnti... spdu-1 for rnti( say 384) is calculated as :- Sfn can range from 0~1023( and will start again once 1023 reached) and for each sfn (slot can range from 0~9) Thanks in Advance, Br, Anupam
