Forum Discussion
Helper IIIReplace Value based on latest date - how to apply the solution
Hi,
I want to replace project name ("Column6" of my table) for each project number ("Project" column of my table) with the name used for most fresh "Extraction Date".
I found the solution it here: Solved: Re: Replace Value Based on latest date - Microsoft Power BI Community , I just do not know how to apply it to my scenario.
Here is my code:
let
Source = Folder.Files("C:\REFRESH_ALL\Raw_Data"),
#"Added Excel Content Table" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content]), type table),
#"Removed Other Columns" = Table.SelectColumns(#"Added Excel Content Table",{"Custom"}),
#"Expanded ExcelContent" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered ExcelSheets" = Table.SelectRows(#"Expanded ExcelContent", each ([Custom.Kind] = "Sheet") and ([Custom.Hidden] = false)),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered ExcelSheets",{"Custom.Data"}),
#"Promote Headers" = Table.AddColumn(#"Removed Other Columns1", "PromoteHeaders", each Table.PromoteHeaders([Custom.Data])),
#"Removed noHeaders" = Table.RemoveColumns(#"Promote Headers",{"Custom.Data"}),
#"Expanded PromoteHeaders" = Table.ExpandTableColumn(#"Removed noHeaders", "PromoteHeaders", {"EXTRACTION DATE", "Parent Project", "Column3", "Parent/Child Indicator", "Project", "Column6", "Activity Element", "1st Level WBS Status", "Applicant", "Budget Date", "Budget Indicator", "Collective ID", "Final Billing Indicator (FNBL)", "FNBL Indicator Date", "Installation site (Ship-to)", "Column16", "Market", "Network", "Column19", "Network Activity", "Column21", "Plant", "Column23", "Project Group", "Project Profile", "Column26", "Project Status", "Net Sales Price", "Budget Cost", "Forecast Cost", "Estimated Remaining", "Committed Cost", "Actual Cost"}, {"EXTRACTION DATE", "Parent Project", "Column3", "Parent/Child Indicator", "Project", "Column6", "Activity Element", "Applicant", "Budget Date", "Budget Indicator", "Installation site (Ship-to)", "Column16", "Market", "Network", "Column19", "Network Activity", "Column21", "Plant", "Column23", "Project Group", "Project Profile", "Column26", "Project Status", "Net Sales Price", "Budget Cost", "Forecast Cost", "Estimated Remaining", "Committed Cost", "Actual Cost"}),
// replace project names with newest extraction date
#"Replace Project Names" = Table.Combine(#"Expanded PromoteHeaders",Table.Group(#"Expanded PromoteHeaders","Project",{"n",each let NewLocation=Table.First(Table.Sort(_,{"EXTRACTION DATE",1}))[Column6] in Table.ReplaceValue(_,null,NewLocation,(x,y,z)=>z,{"Column6"})})[n]),
#"Filtered Rows"
in #"Replace Project Names" I wanted to use the solution of attached problem, by mimicing the formula used there:
=Table.Combine(Table.Group(Source,"Name",{"n",each let NewLocation=Table.First(Table.Sort(_,{"Date",1}))[Location] in Table.ReplaceValue(_,null,NewLocation,(x,y,z)=>z,{"Location"})})[n]
however I am getting the ERROR of:
Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]
could you please help me understand what am I doing wrongly and how to have the newest project names (covered in "Column6" column) assigned to project codes (covered in "Project" column) using "Extraction date" column as the sorting argument for each "Project"?
thank you
4 Replies
- AlexisOlson
Super User
That code is pretty difficult for me to read.
Here's something similar that should be clearer to follow:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiMDQzMQZWqgFKuDImwJpAwtIcJGQLYzRNgcSBkbm8KFXSDCFiDVpujCRgZIwsZAthtY2AAkbGRsgCYMsdIAodoVYgjIUYZmQNWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, ProjectName = _t, Year = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", Int64.Type}, {"ProjectName", type text}, {"Year", Int64.Type}, {"Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ProjectID"}, {{"ProjectName", each Table.Max(_, "Year")[ProjectName], type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ProjectID"}, #"Grouped Rows", {"ProjectID"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"ProjectName"}, {"ProjectNameLatest"}) in #"Expanded Grouped Rows"Let me know if you have questions.
Note: This ^^ is analogous to Approach #3 I wrote about here:
DraszorHi liuqi_pbi,
this is a very good advise. thank you. I worked out the example and placed in on my Google_Drive.
you can notice that Project Names do change with time. I would like to keep (for all the same Project numbers, irrespectively of Extractin Date) the latest name for a given project number.
that means - for project number WR10849757, I should see Red 11 name for all extraction dates
BR
Draszor
- wdx223_Daniel
Community Champion
#"Replace Project Names" = Table.Combine(Table.Group(#"Expanded PromoteHeaders","Project",{"n",each let NewLocation=Table.First(Table.Sort(_,{"EXTRACTION DATE",1}))[Column6] in Table.ReplaceValue(_,null,NewLocation,(x,y,z)=>z,{"Column6"})})[n])
