Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Alright, I feel stupid for multiple reasons but foremost I can't find how to upload my data so if you have some help on that I'd be happy to upload it.
I have a python script that does what I want but I'm trying to move everything into Power BI for ease of use.
I have three columns: Primary Key, Dates, and a code.
I want to count consecutive instances of each code so if it appears 3 times in a row (in 3 consecutive rows) I want the last instance of it to say 3 in a new 4th column.
Primary Key | Code | Attendance Date | New Column |
1 | P | 1/1/11 | |
1 | P | 1/2/11 | 2 |
1 | A | 1/3/11 | 1 |
1 | P | 1/4/11 | 1 |
1 | A | 1/5/11 | |
1 | A | 1/6/11 | 2 |
Solved! Go to Solution.
Hello
this could come from a different sort order after expanding.
here now a solution that should definitily work
let
Source = #table
(
{"Primary Key","Attendance Code","Attendance Date"},
{
{"1","P","40544"}, {"1","P","40575"}, {"1","A","40603"}, {"1","P","40634"}, {"1","A","40664"}, {"1","A","40695"}
}
),
ToDate = Table.TransformColumns
(
Source,
{
{
"Attendance Date",
each Date.From(Number.From(_)),
type date
}
}
),
CreateIndex = Table.Buffer(Table.AddIndexColumn
(
ToDate,
"FirstIndex",
1,
1
)),
Group = Table.Group
(
CreateIndex,
{"Attendance Code"},
{{"AllRows", (tableint) => let
AddIndex = Table.AddIndexColumn(tableint,"Index",1),
AddColumn = Table.AddColumn(AddIndex,"New Column", each if _[Index]=Table.RowCount(AddIndex) then Table.RowCount(AddIndex) else null),
DeleteIndex = Table.RemoveColumns(AddColumn,"Index")
in
DeleteIndex}},
GroupKind.Local,
(group,current)=> if group[Attendance Code]=current[Attendance Code] then 0 else 1),
DeleteOther = Table.SelectColumns(Group,{"AllRows"}),
ExpandTable = Table.ExpandTableColumn(DeleteOther, "AllRows", {"Primary Key", "Attendance Code", "Attendance Date","New Column","FirstIndex"}, {"Primary Key", "Attendance Code", "Attendance Date", "New Column","FirstIndex"}),
ChangeType = Table.TransformColumnTypes(ExpandTable,{{"New Column", Int64.Type}, {"Attendance Date", type date}, {"Attendance Code", type text}, {"Primary Key", type text}, {"FirstIndex", Int64.Type}}),
Sort = Table.Sort(ChangeType,{{"FirstIndex", Order.Ascending}}),
DeleteFirstIndex = Table.RemoveColumns(Sort,{"FirstIndex"})
in
DeleteFirstIndex
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @davidgaribaldi
this solution requires some customized m-code as it would not be possible to do this with the UI. Involves some Table.Group with GroupKind.Local and a special handling of the table. Here the complete solution
let
Source = #table
(
{"Primary Key","Code","Attendance Date"},
{
{"1","P","40544"}, {"1","P","40575"}, {"1","A","40603"}, {"1","P","40634"}, {"1","A","40664"}, {"1","A","40695"}
}
),
ToDate = Table.TransformColumns
(
Source,
{
{
"Attendance Date",
each Date.From(Number.From(_)),
type date
}
}
),
Group = Table.Group
(
ToDate,
{"Code"},
{{"AllRows", (tableint) => let
AddIndex = Table.AddIndexColumn(tableint,"Index",1),
AddColumn = Table.AddColumn(AddIndex,"New Column", each if _[Index]=Table.RowCount(AddIndex) then Table.RowCount(AddIndex) else null),
DeleteIndex = Table.RemoveColumns(AddColumn,"Index")
in
DeleteIndex, type table [Primary Key=text, Code=text, Attendance Date=date]}},
GroupKind.Local,
(group,current)=> if group[Code]=current[Code] then 0 else 1),
DeleteOther = Table.SelectColumns(Group,{"AllRows"}),
ExpandTable = Table.ExpandTableColumn(DeleteOther, "AllRows", {"Primary Key", "Code", "Attendance Date","New Column"}, {"Primary Key", "Code", "Attendance Date", "New Column"})
in
ExpandTable
it all happens in the table.group function. Check it out here
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Sorry but when I close and apply it comes up with errors for each row. I figured out it doesn't like the ExpandColumns step. Do you have any ideas why that might be? When I go to look at the errors it says there are none.
It says I have 42,367 errors but then when I go to see the errors it can't find any. I removed the Expand Columns step and the errors went away so it must be something in that step. I think it might be tacking the New Column onto the table even though it wasn't in the group step that's causing the error.
Hello @davidgaribaldi
could you please go to the DeleteOther-step and click on one table in the "AllRows"-column with a few lines to goto this table. Check out if the there are some errors and please make a screenshot of it.
Jimmy
I clicked on a couple and there doesn't appear to be any errors. All the data is correctly calculating.
*EDIT: Okay I just expanded the columns without adding the New Column and it still creates those errors so just the act of expanding is creating errors somewhere.
Hello @davidgaribaldi
You have to use this syntax for the last step. Please check also if your first column is named like "Primary key" .. otherwise change that too. The error was caused by different column names, meaning, you have provided data example with the wrong column names.
ExpandTable = Table.ExpandTableColumn(DeleteOther, "AllRows", {"Primary Key", "Attendance Code", "Attendance Date","New Column"}, {"Primary Key", "Attendance Code", "Attendance Date", "New Column"})
Hope it helps
Jimmy
Alright, I changed my columns headers to what I originally said they were and used your script but am still gettings the errors. There is something about the expanding that it just doesn't like. When I go to the data view it loses all of the primary keys for some reason.
Hello @davidgaribaldi
are you sure "Primary Key" is exactly written like this in your database? Be aware that Power Query is case sensitive.
Jimmy
Yes, Primary Key is written exactly like that. My best guess is during the grouping process, the primary key is lost because it loses connection with the rest of the data. I'm not sure but as it I can't use this script which is a shame because it clearly works. I just wish Power BI gave actual error codes so you knew what was going on but if you aren't experiencing this when you Close and Apply, I'm at a loss.
Hello @davidgaribaldi
have revised the code.
let
Source = #table
(
{"Primary Key","Attendance Code","Attendance Date"},
{
{"1","P","40544"}, {"1","P","40575"}, {"1","A","40603"}, {"1","P","40634"}, {"1","A","40664"}, {"1","A","40695"}
}
),
ToDate = Table.TransformColumns
(
Source,
{
{
"Attendance Date",
each Date.From(Number.From(_)),
type date
}
}
),
Group = Table.Group
(
ToDate,
{"Attendance Code"},
{{"AllRows", (tableint) => let
AddIndex = Table.AddIndexColumn(tableint,"Index",1),
AddColumn = Table.AddColumn(AddIndex,"New Column", each if _[Index]=Table.RowCount(AddIndex) then Table.RowCount(AddIndex) else null),
DeleteIndex = Table.RemoveColumns(AddColumn,"Index")
in
DeleteIndex}},
GroupKind.Local,
(group,current)=> if group[Attendance Code]=current[Attendance Code] then 0 else 1),
DeleteOther = Table.SelectColumns(Group,{"AllRows"}),
ExpandTable = Table.ExpandTableColumn(DeleteOther, "AllRows", {"Primary Key", "Attendance Code", "Attendance Date","New Column"}, {"Primary Key", "Attendance Code", "Attendance Date", "New Column"}),
ChangeType = Table.TransformColumnTypes(ExpandTable,{{"New Column", Int64.Type}, {"Attendance Date", type date}, {"Attendance Code", type text}, {"Primary Key", type text}})
in
ChangeType
Hope it works now
Jimmy
It does look like it's now loading without errors so that's good!
but now I can actually test the data and it looks like it's wrong. As you can see those 3 A's aren't being counted as a unit.
Hello
this could come from a different sort order after expanding.
here now a solution that should definitily work
let
Source = #table
(
{"Primary Key","Attendance Code","Attendance Date"},
{
{"1","P","40544"}, {"1","P","40575"}, {"1","A","40603"}, {"1","P","40634"}, {"1","A","40664"}, {"1","A","40695"}
}
),
ToDate = Table.TransformColumns
(
Source,
{
{
"Attendance Date",
each Date.From(Number.From(_)),
type date
}
}
),
CreateIndex = Table.Buffer(Table.AddIndexColumn
(
ToDate,
"FirstIndex",
1,
1
)),
Group = Table.Group
(
CreateIndex,
{"Attendance Code"},
{{"AllRows", (tableint) => let
AddIndex = Table.AddIndexColumn(tableint,"Index",1),
AddColumn = Table.AddColumn(AddIndex,"New Column", each if _[Index]=Table.RowCount(AddIndex) then Table.RowCount(AddIndex) else null),
DeleteIndex = Table.RemoveColumns(AddColumn,"Index")
in
DeleteIndex}},
GroupKind.Local,
(group,current)=> if group[Attendance Code]=current[Attendance Code] then 0 else 1),
DeleteOther = Table.SelectColumns(Group,{"AllRows"}),
ExpandTable = Table.ExpandTableColumn(DeleteOther, "AllRows", {"Primary Key", "Attendance Code", "Attendance Date","New Column","FirstIndex"}, {"Primary Key", "Attendance Code", "Attendance Date", "New Column","FirstIndex"}),
ChangeType = Table.TransformColumnTypes(ExpandTable,{{"New Column", Int64.Type}, {"Attendance Date", type date}, {"Attendance Code", type text}, {"Primary Key", type text}, {"FirstIndex", Int64.Type}}),
Sort = Table.Sort(ChangeType,{{"FirstIndex", Order.Ascending}}),
DeleteFirstIndex = Table.RemoveColumns(Sort,{"FirstIndex"})
in
DeleteFirstIndex
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
@Jimmy801 Seriously thank you so much, this looks like it did the trick. Thank you again for taking all this time to work with me. It is very appreciated!
Well done! Yes, I entered my source as my table and it worked perfectly. Thanks a lot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.