Forum Discussion

raymondwkmok's avatar
raymondwkmok
Frequent Visitor
1 year ago
Solved

Power BI Question on Using M - Table.SelectRows

Hi there, I've set the following formula for retrieving the information from the next row of the same dataset; but the outcomes from the search based on the formula below  is "null". Table.Sele...
Need Help
  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    Hi raymondwkmok ,

    Thank you for reaching out to the Microsoft Forum Community.

    The solution provided by SacheeTh  has successfully addressed your query. In addition to his solution, here are some suggestions that might help you manage column name conflicts in your Power BI queries:

    The error you're encountering occurs because the Table.join operation results in duplicate column names, which Power BI cannot handle (e.g., two columns named "Store"). To resolve this, you can:

    1. Rename Duplicate Columns: Open Power Query Editor, identify any duplicate columns, and rename them to avoid conflicts.
    2. Use Aliases: If renaming isn't an option, create aliases to differentiate columns, such as adding a suffix like
      "store1".
    3. Check the Data Source: Ensure your data source doesn't have duplicate column names. If it does, modify the source data to remove duplicates.

    If you have any further questions, feel free to let me know.

raymondwkmok's avatar
raymondwkmok
Frequent Visitor
1 year ago

Hi ZhangKun,

Thanks for the response. 

In short, I've grouped the entire data into a column named "All". Then use the above formaula for retrieving the information from the next row of the same dataset by: -
1. Grouping all data rows into a column named [All];
2. Creating in the formula a Dataset 1 by adding Index Column (start from 1) at [All];
3. Creating in the formula a Dataset 2 by adding Index Column (start from 0) at [All];
4. Map the same Index at Dataset 1 & Dataset 2 then retrive the next row at Dataset 2

So, the source data is indeed being grouped in a column named [All].

Thanks
Raymond

  • ZhangKun's avatar
    ZhangKun
    Icon for Super User rankSuper User
    1 year ago

    i think i got what you mean, and i give you two implementations. if its not what you want, you can try it with a line that is commented.

    let
    Source = Table.FromValue(List.Transform({1, 10, 100}, each Table.FromValue({_.._+9})), [DefaultColumnName = "All"]),
    AddCutomColumn = Table.AddColumn(Source, "custom", each 
        let 
            tbl1 = Table.AddIndexColumn([All], "Index", 1), 
            tbl2 = Table.AddIndexColumn([All], "Index", 0), 
            result = Table.AddColumn(tbl1, "next Value", each try tbl2{[Index = [Index]]}[Value] otherwise null)
            //result = Table.AddColumn(tbl2, "next Value", each try tbl1{[Index = [Index]]}[Value] otherwise null)
        in result
    )
in
    AddCutomColumn

     or

    let
    Source = Table.FromValue(List.Transform({1, 10, 100}, each Table.FromValue({_.._+9})), [DefaultColumnName = "All"]),
    AddCutomColumn = Table.AddColumn(Source, "custom", each 
        let 
            //valueList = {null} & List.RemoveLastN([All][Value]), 
            valueList = List.RemoveFirstN([All][Value]), 
            names = Table.ColumnNames([All]) & {"next value"}
        in 
            Table.FromColumns(Table.ToColumns([All]) & {valueList}, names)
    )
in
    AddCutomColumn