You could merge on two columns : Index & Country/Region Eliminating the need for further if statement in calculated column. I've looked for the possibility to merge based on other operators that "=". Like a merge if one value "
Hi! The video is great i have adopted almost adopted it to our budget system, although i faced an issue that sometimes by the end of period sum is lesser then in previous period, thus i will need to make sure that the sum in new column doesn't go lower then 0. Can you help me modify formula?
Hello, Thank you for the video! I have a question about file transformation with Excel's power query and, more specifically, creating an index column that starts counting rows from the last populated row in every excel file. I take a bunch of excel files from a folder and transform them using one step for grouping by column with "source name"; in the next step, I add a custom column with the following formula: Table.AddIndexColumn([Details], "Index", 1, 1, Int64.Type), after that "Expanded Custom" as expand all needed columns, including an index column. This works great if the row (index number) I need for the following transformation is the same every time. I use these steps in one of my excel transformation models. Now I have almost the same business case, but there are differences in the row (index number) that I need to sort for my purposes. The row I am looking for in each excel file is the fifth populated from up from the bottom. I would really appreciate any advice I can handle this case.
Imke has a way to get value from previous/next row that is much faster than merging (at least in my case)! It might be worth sharing in a video so people are aware of the option if they’re running into performance issues
Hi, Can you please tell me how to achieve below scenario. I have 1 table 1 col in 3 tabs with same date slicer. Based on slicer selection column values sum up and show in 4th tab. (Ex. Tab1 - Date slicer : jan 1, 2018. Col - 10 Tab 2 - Date slicer : oct 1, 2019, Col - 15 Tab 3 - Date slicer : feb 1, 219, Col - 25 Tab 4 - No date slicer. Col should display 50 (tab1+tab2+tab3) How to write DAX??
@@CurbalEN shape/ format is fix: only 3 columns: account (unique), account name, amount. Data is generated on YTD basis. Any similar case familiar to you, to get automatically MTD's, from YTD's?
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules. what does it mean please clarify in power bi ,
Conceptually though. I could imagine that the table could be grouped by 'Country' column (Table.Group) then add the function (Table.TransformColumns) where the list of unique numbers (cases) accompanied by the dates could be transformed into the increase of the case numbers by comparing two lists (original one and the one looks at the value in the next index in the order) using List.Transform.
It is important that the rows are properly sorted before adding the index columns. Looks like the source file was already in proper order, but that is not always the case.
Very helpful! Thank you so much!
You could merge on two columns :
Index & Country/Region
Eliminating the need for further if statement in calculated column.
I've looked for the possibility to merge based on other operators that "=". Like a merge if one value "
Of course you can do that! Thank you!
I have tried with merging on both, with different file and it made a lot of problems, didn't work. Few raws wouldn't merge and so on
Nice, now I understand things better! Thanks 😘
Hi! The video is great i have adopted almost adopted it to our budget system, although i faced an issue that sometimes by the end of period sum is lesser then in previous period, thus i will need to make sure that the sum in new column doesn't go lower then 0. Can you help me modify formula?
Hello, Thank you for the video! I have a question about file transformation with Excel's power query and, more specifically, creating an index column that starts counting rows from the last populated row in every excel file. I take a bunch of excel files from a folder and transform them using one step for grouping by column with "source name"; in the next step, I add a custom column with the following formula: Table.AddIndexColumn([Details], "Index", 1, 1, Int64.Type), after that "Expanded Custom" as expand all needed columns, including an index column. This works great if the row (index number) I need for the following transformation is the same every time. I use these steps in one of my excel transformation models. Now I have almost the same business case, but there are differences in the row (index number) that I need to sort for my purposes. The row I am looking for in each excel file is the fifth populated from up from the bottom. I would really appreciate any advice I can handle this case.
Stop apologizing. You have given us so much. My cup runneth over.
It is mutual!
Una buena solución, gracias por compartir. Saludos Ruth
Saludos 🖖
Great video! I can not find the link with the DAX method? Can somebody help me where I can find the link?
Here it is:
m.th-cam.com/video/mLZ4KISPcZ8/w-d-xo.html
And the latest one on the topic:
m.th-cam.com/video/fAinaG2gGq4/w-d-xo.html
@@CurbalEN Thank you very much!
Imke has a way to get value from previous/next row that is much faster than merging (at least in my case)! It might be worth sharing in a video so people are aware of the option if they’re running into performance issues
Will do!
Hi, Can you please tell me how to achieve below scenario. I have 1 table 1 col in 3 tabs with same date slicer. Based on slicer selection column values sum up and show in 4th tab. (Ex. Tab1 - Date slicer : jan 1, 2018. Col - 10
Tab 2 - Date slicer : oct 1, 2019, Col - 15
Tab 3 - Date slicer : feb 1, 219, Col - 25
Tab 4 - No date slicer. Col should display 50 (tab1+tab2+tab3)
How to write DAX??
Nice. What about "reverse YTD", like two, many YTD (per dates) to get MTD amount, for finance, exaple P&L ? Similar approach or?
It depends of the shape of the data?
@@CurbalEN shape/ format is fix: only 3 columns: account (unique), account name, amount. Data is generated on YTD basis. Any similar case familiar to you, to get automatically MTD's, from YTD's?
With some good all DAX you should be able to calculate MTD? Post in the power bi community for more specific instructions!
@@CurbalEN thank you!
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules. what does it mean please clarify in power bi ,
Conceptually though. I could imagine that the table could be grouped by 'Country' column (Table.Group) then add the function (Table.TransformColumns) where the list of unique numbers (cases) accompanied by the dates could be transformed into the increase of the case numbers by comparing two lists (original one and the one looks at the value in the next index in the order) using List.Transform.
Sounds like a plan!!
I can't find the DAX video regarding this topic. What is the name of it?
Here:
m.th-cam.com/video/mLZ4KISPcZ8/w-d-xo.html
@@CurbalEN cool! Thanks a lot 😊
It is important that the rows are properly sorted before adding the index columns. Looks like the source file was already in proper order, but that is not always the case.
Correct, at least when you create the indexes
I agree. Join Date to Date minus 1 is possible if dates were sequencial
can't we get the value from [Index]-1 ???
Thank you so much...