Add column to show less credit each time a class is taken

[Greenterer]

Using Power Query, I'm trying to add a column to give employees credit for each class that is taken.  The same class can be taken more than one time.  The first time a particular class is completed, the employee receives one credit.  Each subsequent time that same class is completed is worth just .5 credit.  The data I have would look like this:

Name    Class         Date
Bob       Writing     4/7/2024
Bob       Sales         4/8/2024

Bob       Writing     4/9/2024

Bob       Writing     4/10/2024

The goal is to add a column, Credit, so that the table then looks like this

Name    Class         Date             Credit
Bob       Writing     4/7/2024          1
Bob       Sales         4/8/2024          1

Bob       Writing     4/9/2024           .5

Bob       Writing     4/10/2024        .5

[dufoq3]

Hi @Greenterer, another solution:

If you don’t necessarily need the same sort order, delete the SortedRows and AddedIndex steps.

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRCi/KLMnMSweyTPTN9Y0MjEyUYnVgksGJOanFYCkLdClkfZb4JA0NoLKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Class = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "en-US"),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
    Ad_Credit = Table.Combine(Table.Group(AddedIndex, {"Name", "Class"}, {{"T", each Table.FromColumns(Table.ToColumns(_) & {{1} & List.Repeat({0.5}, Table.RowCount(_)-1)}) , type table}})[T]),
    ReplacedType = Value.ReplaceType(Ad_Credit, Value.Type(Table.FirstN(AddedIndex, 0) & #table(type table[Credit=number], {}))),
    SortedRows = Table.RemoveColumns(Table.Sort(ReplacedType,{{"Index", Order.Ascending}}), {"Index"})
in
    SortedRows

and another one 😉

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRCi/KLMnMSweyTPTN9Y0MjEyUYnVgksGJOanFYCkLdClkfZb4JA0NoLKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Class = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "en-US"),
    Buffer = Table.Buffer(Table.SelectColumns(ChangedType,{"Name", "Class", "Date"})),
    MergedQueries = Table.NestedJoin(Buffer, {"Name", "Class"}, ChangedType, {"Name", "Class"}, "Buffer", JoinKind.LeftOuter),
    Ad_Credit = Table.RemoveColumns(Table.AddColumn(MergedQueries, "Credit", each if List.PositionOf([Buffer][Date], [Date]) = 0 then 1 else 0.5, Int64.Type), {"Buffer"}, MissingField.Ignore)
in
    Ad_Credit

[v-menakakota]

Hi @Greenterer ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

Select the table you want to add the column to.
You can do this in either Data view or Model view.

Click on the "New Column" button located in the ribbon
Write the below DAX:

Now new column with Credit name will be created.
Select Table View then will be able to see Credit column as below:

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

Regards,
Menaka

[Greenterer]

Thank you.

[OwenAuger · Accepted solution]

Hi @Greenterer 

I would personally do something similar to the code shown below (paste into a blank query Advanced Editor to test).

Rough description:

1. Group by Name and Class and compute Earliest Date.
2. Join to the original table.
3. If Date = Earliest Date then Credit = 1 otherwise 0.5

let
  Source = #table(
    type table [Name = text, Class = text, Date = date],
    {
      {"Bob", "Writing", #date(2024, 4, 7)},
      {"Bob", "Sales", #date(2024, 4, 8)},
      {"Bob", "Writing", #date(2024, 4, 9)},
      {"Bob", "Writing", #date(2024, 4, 10)}
    }
  ),
  FirstDate = Table.Group(
    Source,
    {"Name", "Class"},
    {"FirstDate", each List.Min([Date]), type date}
  ),
  #"Join FirstDate" = Table.Join(Source, {"Name", "Class"}, FirstDate, {"Name", "Class"}),
  #"Add Credit" = Table.AddColumn(
    #"Join FirstDate",
    "Credit",
    each if [Date] = [FirstDate] then 1 else 0.5,
    type number
  ),
  #"Remove FirstDate" = Table.RemoveColumns(#"Add Credit", {"FirstDate"})
in
  #"Remove FirstDate"

Does this or some variation work for you?

[Greenterer]

This method is going to work.  Thank you.