Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone. I've been trying to find a solution to my index problem, but still not getting the expected results. Let me explain.
I have 3 important columns (PRODDATE, MACHINE, ITEM TYPE) where index 1 is expected to appear in each different PRODDATE or MACHINE.
I will put the same index and sequentially if PRODDATE, MACHINE, ITEM TYPE are of the same type (like the column index in pic2)
Is this possible?
Please see my pictures for a better understanding.
Solved! Go to Solution.
Use this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY8xCsAgDEXvklnQfIXasToXuov3v0YVaju0idAhZHj8vPxSiC3DwgFk6Njd0tbWxgdP1YgY8JB46vFJWssys4ZDUMXff8cbSzT3v65SeN/Oj1mgwyxijSrmpJrTX/Ok88BxpVpP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PRODATE = _t, MACHINE = _t, #"ITEM TYPE" = _t, KG = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PRODATE", type date}, {"MACHINE", type text}, {"ITEM TYPE", type text}, {"KG", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"PRODATE", "MACHINE"}, {{"Temp", each _, type table [PRODATE=nullable date, MACHINE=nullable text, ITEM TYPE=nullable text, KG=nullable number]}}, GroupKind.Local),
#"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])),
fxProcess = (Tbl)=>
let
#"Grouped Rows" = Table.Group(Tbl, {"PRODATE", "MACHINE", "ITEM TYPE"}, {{"All", each _, type table [PRODATE=nullable date, MACHINE=nullable text, ITEM TYPE=nullable text, KG=nullable number]}}, GroupKind.Local),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"PRODATE", "MACHINE", "ITEM TYPE"}),
#"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"PRODATE", "MACHINE", "ITEM TYPE", "KG"}, {"PRODATE", "MACHINE", "ITEM TYPE", "KG"})
in
#"Expanded All",
#"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"fxProcess"}),
#"Expanded fxProcess" = Table.ExpandTableColumn(#"Removed Other Columns", "fxProcess", {"PRODATE", "MACHINE", "ITEM TYPE", "KG", "Index"}, {"PRODATE", "MACHINE", "ITEM TYPE", "KG", "Index"})
in
#"Expanded fxProcess"
Record.AddFiled([],y[ITEM TYPE],"")
ah~~,there is a typo, that should be Record.AddField
= Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{{},[]},(x,y)=>let a=Text.Format("#[PRODDATE]-#[MACHINE]",y),b=List.RemoveLastN(Record.FieldOrDefault(x{1},a,{}),each _=y[ITEM TYPE])&{y[ITEM TYPE]} in {x{0}&{y&[INDEX=List.Count(b)]},x{1}&Record.AddField([],a,b)}){0})
NewStep=Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{{},[]},(x,y)=>let a=Text.Format("#[PRODDATE]-#[MACHINE]",y),b=Record.FieldOrDefault(x{1},a,[])&Record.AddFiled([],y[ITEM TYPE],"") in {x{0}&{y&[INDEX=Record.FieldCount(b)]},x{1}&Record.AddField([],a,b)}){0})
Hii @wdx223_Daniel is the formula added to the advance editor or to a new column in power query?
click here to add a new step, and replace all the code after equal symbol with my code in the formula area, and delete "NewStep=", then change "PreviousStepName" to your last real step name which can be found in the right pane.
Record.AddFiled([],y[ITEM TYPE],"")
ah~~,there is a typo, that should be Record.AddField
Hii @wdx223_Daniel its works, but the result is not same with DESIRED INDEX (picture2). Is there any idea for this solution?
= Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{{},[]},(x,y)=>let a=Text.Format("#[PRODDATE]-#[MACHINE]",y),b=List.RemoveLastN(Record.FieldOrDefault(x{1},a,{}),each _=y[ITEM TYPE])&{y[ITEM TYPE]} in {x{0}&{y&[INDEX=List.Count(b)]},x{1}&Record.AddField([],a,b)}){0})
Use this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY8xCsAgDEXvklnQfIXasToXuov3v0YVaju0idAhZHj8vPxSiC3DwgFk6Njd0tbWxgdP1YgY8JB46vFJWssys4ZDUMXff8cbSzT3v65SeN/Oj1mgwyxijSrmpJrTX/Ok88BxpVpP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PRODATE = _t, MACHINE = _t, #"ITEM TYPE" = _t, KG = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PRODATE", type date}, {"MACHINE", type text}, {"ITEM TYPE", type text}, {"KG", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"PRODATE", "MACHINE"}, {{"Temp", each _, type table [PRODATE=nullable date, MACHINE=nullable text, ITEM TYPE=nullable text, KG=nullable number]}}, GroupKind.Local),
#"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])),
fxProcess = (Tbl)=>
let
#"Grouped Rows" = Table.Group(Tbl, {"PRODATE", "MACHINE", "ITEM TYPE"}, {{"All", each _, type table [PRODATE=nullable date, MACHINE=nullable text, ITEM TYPE=nullable text, KG=nullable number]}}, GroupKind.Local),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"PRODATE", "MACHINE", "ITEM TYPE"}),
#"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"PRODATE", "MACHINE", "ITEM TYPE", "KG"}, {"PRODATE", "MACHINE", "ITEM TYPE", "KG"})
in
#"Expanded All",
#"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"fxProcess"}),
#"Expanded fxProcess" = Table.ExpandTableColumn(#"Removed Other Columns", "fxProcess", {"PRODATE", "MACHINE", "ITEM TYPE", "KG", "Index"}, {"PRODATE", "MACHINE", "ITEM TYPE", "KG", "Index"})
in
#"Expanded fxProcess"