Getting a value into a column from the same table

[xpisoverated]

I'm sure i'm missing something obvious, but here goes

I have a sharepoint folder with 3 csv files in it, i bring them all into a single table via the folder import.

One of the columns in the table is the filename, e.g. June.csv, July.csv, etc.

The table is formatted something like this

ID, Name, Score, Filename

So in most cases each ID will appear 3 times, once for each of the csv files, e.g

1,A,12,May.csv

1,A,15,June.csv

1,A,15,July.csv

The scores may change from file to file.

For each row of data in the file i would like to create a column that shows the score value for a particular month, i.e. adding a column something like

ID, Name, Score, Filename,May,June,July

So the data in the table would end up as

1,A,12,May.csv,12,15,15

1,A,15,June.csv,12,15,15

1,A,15,July.csv,12,15,15

How would i syntax the column dax to do this ?

[v-aatheeque]

Hi @xpisoverated

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

[v-aatheeque]

Hi @xpisoverated 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[jgeddes]

@MasonMA has provided the easiest solution.
If you want to maintain your original table format and you do not want to use table joins, here is a solution. It is likely overkill for your solution but it explores table grouping, nested lists, dynamic column renaming, and dynamic column type setting. Cheers.

let
    //example data
    Source = 
    Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WMlTSUXIEYkMjIOGbWKmXXFymFKuDJGEKJLxK81JxyeQg9BjBZEzQDINLmKNoiQUA", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Score = _t, Filename = _t]
    ),
    //type setting of initial data
    initial_type_set = 
    Table.TransformColumnTypes(
        Source,
        {
            {"ID", Int64.Type}, {"Name", type text}, {"Score", Int64.Type}, {"Filename", type text}
        }
    ),
    //get distinct list of filenames
    fileList = 
    List.Distinct(initial_type_set[Filename]),
    //create a type setting list that sets month columns to Int64 and the filename column to text
    typeList = 
    List.Combine(
        {
            List.Zip(
                {
                    List.Transform(fileList, each Text.BeforeDelimiter(_, ".csv")), 
                    List.Repeat({Int64.Type}, List.Count(fileList))
                }
            ), 
            {{"Filename", Text.Type}}
        }
    ),
    //group table by ID and Name using all rows as the aggregate
    group_rows = 
    Table.Group(
        initial_type_set, 
        {"ID", "Name"}, 
        {
            {"AllRows", each _, type table [ID=nullable number, Name=nullable text, Score=nullable number, Filename=nullable text]}
        }
    ),
    //add a column to the nested table that contains list of distinct filenames for the ID Name table
    add_filenames = 
    Table.TransformColumns(
        group_rows, 
        {
            {"AllRows", each let names = [Filename] in Table.AddColumn(Table.Pivot(_, List.Distinct(_[Filename]), "Filename", "Score"), "Filename", each names, type list), type table}
        }
    ),
    //expand the nested table, dropping the '.csv' from the filename columns
    expand_nested = 
    Table.ExpandTableColumn(
        add_filenames, 
        "AllRows", 
        List.Combine({fileList, {"Filename"}}), 
        List.Combine({List.Transform(fileList, each Text.BeforeDelimiter(_, ".csv")), {"Filename"}})
    ),
    //expand the filename list column to new rows
    expand_filenames = 
    Table.ExpandListColumn(
        expand_nested, 
        "Filename"
    ),
    //set the column data types
    final_type_set = 
    Table.TransformColumnTypes(
        expand_filenames, 
        typeList
    )
in
    final_type_set

[Arul]

In the power query,

Click Month column and Pivot under transform using Score as Values.

Output:

[MasonMA]

Since the data already contains the filename, you can do this in Power Query instead by Pivot Column.

Use Score as the Values column. (Under Advanced options, choose Don't Aggregate)

If you still need the original row structure, you can merge this pivoted table back onto the original table. This is generally simpler to maintain than creating one DAX calculated column per month.

[xpisoverated]

Thanks Pivot works perfectly, one question, if i pivot on the date, so in effect my table visual is

Name,Date1,Date2,Date3

Is there an easy was to add a column or measure after date3 that shows if the value in date 1 and 2 has increased, decreased or stayed the same ?

[v-aatheeque · Accepted solution]

Hi @xpisoverated 

Yes, you can definitely add logic to compare values across pivoted dates. Since your table visual is showing Name, Date1, Date2, Date3, you want a measure that checks the difference between Date1 and Date2, then returns a text label like Increased, Decreased, or No Change.