Needing some help with how to build this table. Custom Columns.

[stuntmanpatch]

I am struggling with a solution for this challenge, hoping for some inputs.  I'm trying to build a new table that consolidates the rows down to the uniques Names, then custom column to pull in the data.

The table is on the left.  It is a single table.

On the right is what I am trying to get to.  P4 is the gold standard that I want to compare any other PIDs values.

It's an audit tool that when I select the PID on the table this table on the right should filter down to Column A being the unique Name, column B being that chosen PIDS values, and column B being the gold standard value.

I hope that all makes sense and thank you in advance for any guidance

[stuntmanpatch]

Apologies if my description is vague.  I am learning as I go and it's difficult for me to formalize the problem.  I'll take another stab.  The posts thus far have gotten me close.

I have a single table that includes settings for various PIDS.

The settings that are in PID 25416 are considered the default settings.  Ultimatley, I am trying to put a table or display that would compare the settings say from PID 101 or PID 105, to the setting values in PID 25416.

The new column should return the respective value for PID 25416, for the respective Name, ie, setting 1, 2, etc.

Then I'm thinking in a display I can show these columns side by side to do a quick audit from any PID to the default values.

I've gotten to this point.  The isse with the below is that I do not know how to do the dynamic piece to pull in the relative value based on the Name.  

As in the below, I am pull in the respective value, but it is the value for Setting1 across the board.

What can i do to make the below pull in the value for the respective Name.

[Name] = [Name]  does not work.

Hope this makes it a little more clear.

Thank you all for the inputs.

let
Source = Prod_Custom_Toggle_Config,
AMB = 25416,
#"Added Custom" = Table.AddColumn(Source, "StandardAMB", each List.Max(Table.SelectRows(Source, each AMB = [PID] and [Name]= "Setting1")[IsEnabled]))
in
#"Added Custom"

[jgeddes · Accepted solution]

This something like this what you are looking for?

let
    defaultPID = "25416",
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRCk4tKcnMSwcxI5ViddCEjbALGwOZfpjCJkiqTVHN9sMUNsIubIzdEBO4aiNTE0MzbC5HkzDCpcMYlw6oHbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PID = _t, Name = _t, Enabled = _t]),
    set_types = 
    Table.TransformColumnTypes(
        Source,
        {
            {"PID", Int64.Type}, 
            {"Name", type text}, 
            {"Enabled", type text}
        }
    ),
    defaultTable = 
    Table.SelectRows(Source, each [PID] = defaultPID),
    merge_tables = 
    Table.NestedJoin(
        Source, 
        {"Name"}, 
        defaultTable, 
        {"Name"}, 
        "Source", 
        JoinKind.LeftOuter
    ),
    expand_default_enabled = 
    Table.ExpandTableColumn(
        merge_tables, 
        "Source", 
        {"Enabled"}, 
        {"DefaultEnabled"}
    ),
    sort_table = 
    Table.Buffer(
        Table.Sort(
            expand_default_enabled,
            {
                {"PID", Order.Ascending}, 
                {"Name", Order.Ascending}
            }
        )
    )
in
    sort_table

[stuntmanpatch]

Thank you so much for this.  It worked perfect.  

[v-menakakota]

Hi  @stuntmanpatch  ,
Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank  @speedramps  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Best Regards,
Community Support Team

[Omid_Motamedise]

Could you please provide the sample data as a table here?

[Riny_vE]

Let's assume a set-up in Excel as shown below. Table1 contains your data, selection is a named range for the selected PID.

The following M-code will generate the PQ result table:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each [PID] = selection or [PID] = "P4"),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows", {{"PID", type text}}, "en-001"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows", {{"PID", type text}}, "en-001")[PID]), "PID", "Enabled")
in
#"Pivoted Column"

where selection is a parameter based on the named range.

let
Source = Excel.CurrentWorkbook(){[Name="selection"]}[Content],
Column1 = Source{0}[Column1]
in
Column1

[speedramps]

I want to help you but your description is too vague. Please write it again.

How do you want to derive "Enabled for PID" in the right hand table?

For example 
PHD2 Name 1 = Y  and  PHD3 Name 2 = Y  and Enabled for PID = Y
PHD2 Name 2 = N  but  PHD3 Name 2 = Y  and Enabled for PID = N   why ???
PHD2 Name 3 = N  but  PHD3 Name 3 = Y  and Enabled for PID = N   why ???

Will there only every be one instance of P4 for each Name ?

If there are multiple instances then how should Enanable for PD be derived

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want.
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.