Hi Aye,

Please see attached.

Note: I've removed the earlier questions, changed the title of this post and moved it to the Power Query forum. In future please start a new post for each new question.

Thanks,

Mynda

Thank you so much, Mynda,

If possible, can you please make it '0' to the cell highlighted where 'Rate' is the same.(This will be an additional criteria for this).ref:PQ_FuningReduction1

Another additional criteria is time factor, if the rate was lower at previous 'Date and higher after later 'Date', it is not reduction in funding as well. ref:PQ_FuningReduction2

Please see attached 2 screenshots for my two additional criteria.

Thank you so much.

Regards,

Aye

Hi Aye,

Sorry for the slow reply to this. I've looked at it several times and I'm not clear on what you're trying to achieve. Can you please upload a sample Excel file that shows the before and after desired results?

Thanks,

Mynda

Thanks, Mynda,

I should have mentioned a bit more clearly.

I am also working on different way for this.

Please see attached and I also include "Funding2" sheet for another forum post I made. It is still WIP and I hope all my senarios cover this exercise.

Many thanks, Mynda.

Regards,

Aye

Hi Aye,

Please do not open multiple topics if your data is the same and you just need a different calculation.

See the reply here: https://www.myonlinetraininghub.com/excel-forum/power-query-members/funding2#p8725

Once you clarify the logic, you should be able to use the same solution or to adjust the solution to your logic.

Cheers,

Catalin

Hi Catalin,

I am so sorry that I sort of open multiple topics for this as I was a bit of desperate finding the solution and different scenarios came up prior to getting an answer.

Please close other topic and can we just focus on the one I sent at the latest stage.

First I would like to get a result of '0' for all the rows, if the 'Category' are the same for the same 'ID'. How can I do that first.

Thanks, Catalin.

Regards,

Aye

Hi Aye,

There is already a column in the attached file (also attached at your other posts), that checks if for the same ID, all Dates-Facilities-Categoies are the same.

Basically, that custom function I wrote is filtering the data table to extract only a specific ID. In Custom column, in each row there is a table containing only the data for that ID. Obviously, in that table there is only that ID of the current row, but there can be multiple rows. If the Distinct Count of Dates, Categories and Facilities are equal on these 3 columns (from the filtered table in Custom column, current row), then we can say that all are the same for that ID.

For your last question, to see if all categories are the same for the same ID, I added a new column with this formula:

if List.Count(List.Distinct([Custom][Category])) = 1 then 0 else "more than 1 category for the same id"

Formula translation:

Knowing that in the Custom column, each table has a single ID but there can be multiple rows, a Distinct Count in column Category should be always 1 if all categories are the same.

Thank you so much, Catalin.

I now get the list where more than one category for the same ID.

As I need to figure out reducing over the period, can I have another column to show the result of

"if later (later date) rate for the same ID  is higher than the former (earlier date) rate for the same ID, result will be "0"

Thank you

Regards,

Aye

Hi Aye,

I've modified the custom function, added a sorting step to sort the rate and Date descending. This way, the highest date will be always on the first row on the filtered table from the Custom column. With this in mind, the formula to calculate what you need is very simple ({0} will return the first value in a column):

if [Custom][Rate]{0}>[Rate] then 0 else "latest rate is smaller"

"if the first rate from the Custom column table is higher than the current row rate, show 0"

Thank you so much, Catalin

I am still trying to understand the function you used.

(ID)=>
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Facility", Int64.Type}, {"Days", Int64.Type}, {"Rate", type number}, {"Total", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ID] = ID))
in#"Filtered Rows"

I will try to work with my real data and get back to you shortly.

Thank you once again

Regards,

Aye

There is not much about it, it's just your data table imported into query, then filtered by ID. If you remove the first line, it will be just a normal query:

To transform it into a function, add the variable parameter above the let statement, and use that variable where it's needed in the query:

Note that it's not the last function I used, the last one should have a Sort step.

Hi Catalin,

I am struggling with my live data. to get to the stage I have done following steps in Power Query.

let
Source = Folder.Files("F:Users2018 - 2019Financial ReportingManagement AccountsPower BI reportsMedicareCSV"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from MedicareCSV", each #"Transform File from MedicareCSV"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from MedicareCSV"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from MedicareCSV", Table.ColumnNames(#"Transform File from MedicareCSV"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", Int64.Type}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", Int64.Type}, {"Column20", type text}, {"Column21", Int64.Type}, {"Column22", type text}, {"Column23", type number}, {"Column24", type text}, {"Column25", type number}, {"Column26", type text}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type any}, {"Column67", type any}, {"Column68", type any}, {"Column69", type any}, {"Column70", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column7] = "ADJ SUB DNA" or [Column7] = "ADJ SUB DR" or [Column7] = "ADJ SUB HHH" or [Column7] = "ADJ SUB HHL" or [Column7] = "ADJ SUB HHM" or [Column7] = "ADJ SUB HHN" or [Column7] = "ADJ SUB HLH" or [Column7] = "ADJ SUB HLL" or [Column7] = "ADJ SUB HLM" or [Column7] = "ADJ SUB HMH" or [Column7] = "ADJ SUB HML" or [Column7] = "ADJ SUB HMM" or [Column7] = "ADJ SUB HNH" or [Column7] = "ADJ SUB HNL" or [Column7] = "ADJ SUB HNM" or [Column7] = "ADJ SUB LHH" or [Column7] = "ADJ SUB LHL" or [Column7] = "ADJ SUB LHM" or [Column7] = "ADJ SUB LHN" or [Column7] = "ADJ SUB LLH" or [Column7] = "ADJ SUB LLL" or [Column7] = "ADJ SUB LLM" or [Column7] = "ADJ SUB LLN" or [Column7] = "ADJ SUB LMH" or [Column7] = "ADJ SUB LML" or [Column7] = "ADJ SUB LMM" or [Column7] = "ADJ SUB LMN" or [Column7] = "ADJ SUB LNH" or [Column7] = "ADJ SUB LNL" or [Column7] = "ADJ SUB LNM" or [Column7] = "ADJ SUB LNN" or [Column7] = "ADJ SUB MHH" or [Column7] = "ADJ SUB MHL" or [Column7] = "ADJ SUB MHM" or [Column7] = "ADJ SUB MHN" or [Column7] = "ADJ SUB MLH" or [Column7] = "ADJ SUB MLL" or [Column7] = "ADJ SUB MLM" or [Column7] = "ADJ SUB MMH" or [Column7] = "ADJ SUB MML" or [Column7] = "ADJ SUB MMM" or [Column7] = "ADJ SUB MMN" or [Column7] = "ADJ SUB MNH" or [Column7] = "ADJ SUB MNL" or [Column7] = "ADJ SUB MNM" or [Column7] = "ADJ SUB NHL" or [Column7] = "ADJ SUB NHM" or [Column7] = "ADJ SUB NLL" or [Column7] = "ADJ SUB NLN" or [Column7] = "ADJ SUB NML" or [Column7] = "ADJ SUB NMM" or [Column7] = "ADJ SUB NNL" or [Column7] = "ADJ SUB NNM" or [Column7] = "ADJ SUB NNN" or [Column7] = "ADJ SUB SS3" or [Column7] = "ADJ SUB SS6")),
#"Split Column by Position" = Table.SplitColumn(#"Filtered Rows", "Source.Name", Splitter.SplitTextByPositions({0, 4}, false), {"Source.Name.1", "Source.Name.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Source.Name.1", Int64.Type}, {"Source.Name.2", type text}}),
#"Split Column by Position1" = Table.SplitColumn(#"Changed Type1", "Source.Name.2", Splitter.SplitTextByPositions({0, 4}, true), {"Source.Name.2.1", "Source.Name.2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Source.Name.2.1", type date}, {"Source.Name.2.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Source.Name.2.2"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each true),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Column1", "Column6", "Column8", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","+","1",Replacer.ReplaceText,{"Column10", "Column24", "Column26"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","-","-1",Replacer.ReplaceText,{"Column10", "Column24", "Column26"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value1",{{"Column9", Int64.Type}, {"Column10", Int64.Type}, {"Column24", Int64.Type}, {"Column26", Int64.Type}, {"Column23", type number}, {"Column25", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "Days", each [Column9]*[Column10]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Rate", each [Column23]*[Column24]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Total", each [Column25]*[Column26]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Column9", "Column10", "Column23", "Column24", "Column25", "Column26"}),
#"Split Column by Position2" = Table.SplitColumn(#"Removed Columns2", "Column7", Splitter.SplitTextByPositions({0, 8}, false), {"Column7.1", "Column7.2"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position2",{{"Column7.1", type text}, {"Column7.2", type text}}),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type4",{"Column7.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns3",{{"Column7.2", "ACFI"}, {"Source.Name.1", "Facility"}, {"Source.Name.2.1", "Date"}, {"Column2", "ID"}}),
#"Changed Type5" = Table.TransformColumnTypes(#"Renamed Columns",{{"Days", Int64.Type}, {"Rate", type number}, {"Total", type number}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type5",{{"ACFI", "Category"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns2", "Custom", each #"Transform File from MedicareCSV"([ID])),
#"Removed Columns4" = Table.RemoveColumns(#"Invoked Custom Function",{"Custom"})
in
#"Removed Columns4"

I do not know how to create FilterID function from which step.

During those steps, one Function was created under Query window. However it was automatically generated and I did not do any about that.

Should FilterID function be created by adding column or right click my table to "Create Function"???

I think other steps can be done just after that custom- FilteredID has been done???

I am sorry, please let me know the steps. 

Thank you.

Regards,

Aye

Hi,

You can add a blank query (From Other Sources-Blank Query), then open the Advanced editor and replace what's in there with the code I sent in the last file. Rename this new query as FilterID, then all you need to do is to add a new column to your query using the new function.

You have an example in the last file uploaded.

Thank you so much Catalin.

I am now able to do FilteredID function.

However I am stuck with an error message.

Please see my work sheet and please let me know what did I do wrong.

Thank you so much for your help.

Regards,

Aye