Parsing Issue
Dear Experts, I have a Data like below:- From this I have a to create an Output file with the below logic, (sample of an entry in Output file is below):- HARQ ID, can range from 0 ~15, and Each HARQ can be re-transmitted(RV) in the Sequence 0->2->3->1->0->2->3->1...(create data with all the Harq IDs having the RVs in a full cycle and extended(0-2-3-1-0) System frame number have to be consecutive and after 1023 SystemFrameNumber(Column C) repeats it self. Thanks in Advance, Br, Anupam
Cubeset with multiple Period range
Hey All! need help with creating a Cubeset formula! I've market data at region level for multiple period & brand! I want to know the rank of particular Brand in each region on Specific Period. Formula is working for a particular period but not working on a range! How can I use a range/dynamic period in cubeset. =CUBESET( "ThisWorkbookDataModel", "EXISTS([Brandmaster].[Brand].children, ([State_Master].[State].[Maharashtra], {[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[Dec-2024]}))", "Set", 2, "{([Measures].[S_Value],[State_Master].[State].[Maharashtra],{[Calendar].[Month].[Oct-2024]})}" ) This is working for particular month but when I want to get data for 3 month like Oct-Dec =CUBESET( "ThisWorkbookDataModel", "EXISTS([Brandmaster].[Brand].children, ([State_Master].[State].[Maharashtra], {[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[Dec-2024]}))", "Set", 2, "{([Measures].[S_Value],[State_Master].[State].[Maharashtra],{[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[dec-2024]})}" ) I'm getting error on this. Range is working on set expression but while using same in Sort By (Highlighted Part), I'm getting the error. One way around is passing Each Month separately not tried that but I want it to be dynamic using timeline & using Min/Max function to define the range, it could be 2month or 12 month. Pls help me with this. ThanksPower Query Combine Worksheet
Dear Experts, I have many sheets(>10) sharing only 2 here as an example. Each sheet has a data summary in the end as below, and I want to merge all the sheets into a Single one with all the below data only:- But when , I merge the sheets, in the Column "A", only Time is there but all the stats are gone and not merged:- And after merge all that needed data is lost, and only Time stamps are there:- What wrong , am I doing here? Br, Anupam
KPI dax logic not giving correct counts but table visual does
Hi Team, I have below dax for the Doors open kpi: Doors Open = VAR _count = CALCULATE( MAX( BMSDoorStoreJoin[CreatedOn] ), BMSDoorStoreJoin[Status] = "Open" ) VAR _siteid = MAX(BMSDoorStoreJoin[SiteId]) RETURN CALCULATE( COUNT( BMSDoorStoreJoin[CreatedOn] ), BMSDoorStoreJoin[Status] = "Open"&& BMSDoorStoreJoin[CreatedOn] = _count && BMSDoorStoreJoin[SiteId] = _siteid ) Now, this returns the value of 1 which is wrong according to filtered SiteId(StoreName). My expected output from this logic is I want to find unique count of Doors Open(Site[Sitecode]) for the max of createdon date. For example, the data below for Sitecode 472 we have Status as closed and we don't have open. So it should retun nil. but in this case it returns 1. My table visual also shows correctly that it has only closed status. When i use my kpi logic in that visual it gives correct count. Please advise! PFA file here BMS Chillers Insights.pbix Thanks in advance! SergeiBaklanFilter context playing up with Calculated column logic in report visual
Hi Sir, I have a calculated column as below: Its working fine in this table level. But when I use this column in a table visual in report its not working correctly and its giving 'Yes' in all rows of the table for same deviceid: FYR, screenshot attached: It should contain only rows where there are warnings(Yes). But when i remove the OpenMinutes,Storetime and Door Time from above table, I get the correct output: My expected output for filtered sitecode 73 and deviceid 2452 is i need below columns along with Store Time, Door Time and OpenMinutes(openstateduration table): Store Time comes from Openstateduration table DateTimeRounded which is linked to Door table DateTimeRounded(Both date columns should match): Door Time will be coming from Createdon date of Openstateduration table which is linked to Door table Createdon(Both date columns should match): Modeling for these tables are: Could you please help me fix this issue? Also, much appreciated if you help me with alternative or better solution like pushing Warning logic to power query or creating better modeling for this issue etc. PFA file here B&M (4).pbix Please feel free to let me know if any issues! Thanks in advance! SergeiBaklan
I cannot import an Excel file from OneDrive using Power Query (“Get Data → From Web”) in Excel
For some time now, OneDrive has not been providing me with the full URL of the Excel file in my personal OneDrive folder. Instead, it now gives me a shortened link, which is not accepted by the “Get Data → From Web” option in Excel. Example of the URL format it used to provide: https://onedrive.live.com/redir.aspx?cid=31XXX007X973ee6b&resid=3XXXXXC973EE6B!49999&parId=3XXXXXC973EE6B!46489&authkey=!ANrZXXXSdDCDJ0 Example of the URL format it now provides: https://1drv.ms/t/c/3XXXXXC973ee6b/EWvuc8kHcMcggDE6wwXXXXXBSTsVubH5dQtJKs7ZY8tlZg?e=PdwXXk With the first type of URL, I only had to replace the text "redir.aspx" or, in some cases, the text "embed" with "download", and I could easily import the Excel content using the “Get Data → From Web” option in Excel. This allowed me to work with it in Power Query without any issues. I should mention that I am using my personal OneDrive account. Could you provide a manual or guide on how to import data from an Excel file located in a personal OneDrive account using the “Get Data → From Web” option in Excel? Is there a way to obtain the full file URL in OneDrive so I can import it as I did before? Is there another way to import files from OneDrive using the shortened URL it now provides? En Español: Desde hace un tiempo, onedrive no me entrega la URL completa del archivo excel en mi carpeta de onedrive personal. Ahora me entrega un link corto el cual no es aceptado por la opción de "Obtener Datos --> De la Web" del excel. URL de ejemplo que entregaba hace un tiempo: https://onedrive.live.com/redir.aspx?cid=31XXX007X973ee6b&resid=3XXXXXC973EE6B!49999&parId=3XXXXXC973EE6B!46489&authkey=!ANrZXXXSdDCDJ0 URL de ejemplo que entrega ahora: https://1drv.ms/t/c/3XXXXXC973ee6b/EWvuc8kHcMcggDE6wwXXXXXBSTsVubH5dQtJKs7ZY8tlZg?e=PdwXXk Con la primera opción de URL, solo debía cambiar la el texto "redir.aspx" o en algunos casos el texto "embed" en la URL por el texto "download" y con eso podía importar sin problemas el contenido del excel a través de la opción "Obtener Datos --> De la Web" del excel para posteriormente trabajarlo con power query Debo indicar que estoy utilizando mi cuenta personal del onedrive ¿Pueden incluir algún manual de como importar datos desde un excel ubicado en ondrive personal utilizando la opción "Obtener Datos --> De la Web" del excel?. ¿Se puede obtener la URL completa del archivo en onedrive para poder importarlo como lo hacía anteriormente? ¿Existe otra forma de importar archivos desde onedrive utilizando la URL corta que entrega ahora?Creating Logic to find whether columns in one table matches other columns in other table
Hi Team, I have 2 tables: Door table as below: Store Status table as below: They are modelled as below (let me know if we can better model it-suggestions are welcomed): Now, I need to create a logic(Breach) to find when Door Table - Status column being Open when Store Status table - Status column being Closed (indicated by - 'C'). During this logic, we have to make sure it satisfies below conditions: DateTime column of Store Status table should match the createdon column of Door table Store id column of Store Status table should match the siteid of Door table Output should return 1 if the conditions meets else 0. Could you please help me create a logic for this? PFA file here B&M.pbix Thanks in advance! SergeiBaklanCreate a Power query or Dax to find Open Status more than 1 hour
Hi Team, I have the door table Here I want to find that whether door is being open continuously(having open status & not closed) for more than 1 hour for each DeviceId. Data Description: The Status column shows whether door is open or Closed. CreatedOn column shows time duration. DeviceId column shows Deviceid. My first requirement is: We need to add the minutes(createdon) between each row for same deviceid where the status = open. But where a status = closed (if closed comes in between open) we reset the number of minutes. So we sum by the previous value where status = Open - Something like If current status = open and previous status = open then sum the time and do the below: Sum(previous value + new sum) Where/if status = closed set time count = 0 Sum(previous value + new sum) is going to take into account only the consecutive open status >60 min for same divice id whenever status is closed, this Sum(previous value + new sum) will be reset to/equal to 0 So on the first open status, the count will be 0. Then the next open status it will be time between 2 open statuses, then on the third it will be the time between two open statuses add the previous time.but any close status then time is reset. Could you please help me with a power query logic/dax logic? Second Requirement: This is only a sample data. But we have billions of rows of data in our dataflow that comes from SQL views. So, thought not to use DAX for this calculation in future. we can precalculate the maximum open state duration by preprocessing data (SQL, Power Query(dataflow), Spark, anything else...). So can you help me prepare data upfront. we need to solve the problem with a with a proper data model. for example, create a table with the longest open events upfront (in whatever technique you are comfortable with) Could you please help me to achieve this? PFA file here B&M.pbix Thanks in advance!
