how to combine lookupvalue on multiple columns ?

[Thrasymaque]

hi guys

I am somehow struggling with a little problem 

I try to do a lookupvalue on different columns --> as depending on the line a different column will be field (2 Column can't be field on the same line)

Ex:

Line 1 : Starting Date is on column 4 

Line 2 : Starting Date is on column 8

Line 3 : Starting Date is on column 13

Line 4 : Starting Date is on column 8

etc etc

I succeed to do on the 1st column but I can' succeed to look on the 9th columns I dlike to chase

here is my formula 

StartingDate = LOOKUPVALUE(IT_Portfolio_Milestones[OData_1_Current Start_x002];IT_Portfolio_Milestones[OData_1_Name];MilestonesName[Valeur];IT_Portfolio_Milestones[Title];MilestonesName[Title];)

I want to do the same on 

IT_Portfolio_Milestones[OData_2_Current Start_x002];IT_Portfolio_Milestones[OData_2_Name]

IT_Portfolio_Milestones[OData_3_Current Start_x002];IT_Portfolio_Milestones[OData_3_Name]

...ECT untill 9 🙂 

Any Idea on how to proceed ?

many thx

BR

F.

[lbendlin · Accepted solution]

There are two options to approach this 

- as I mentioned above, use row unpivoting trick in DAX

- run the transform in Power Query to get the data into a more palatable format

Not sure which one you prefer.  Please let me know.

By the way you have a typo in one of the column headers: 2_Name2 should be 2_Name

Here is the Power Query version

let
    Source = Table,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Title"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "Level"}, {"Attribute.2", "Attribute"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute]), "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each [Name] <> null and [Name] <> "")
in
    #"Filtered Rows"

which results in

[Thrasymaque]

Works Perfectly !  A big thanks !

I have been using the script as I never succeed to unpivot properly 😕

For very beginners : Code to be entered in "home" "advanced editor"

[Thrasymaque]

Hello 

please find an exemple following this link

BR

Frederic 

https://drive.google.com/drive/folders/1LPEXdEazP3qwA9dm3Vo8OF1Zx_QqSZE2

[lbendlin]

There is a very neat trick that you need to know. You can transform the columns of a row into a single column table variable.  Once you have done that you can use table row math to fetch the nth row's content.

Please provide sample data in usable format (not as a picture) and show the expected outcome.

[Thrasymaque]

Hello 

thank you for taking the time with your 1st answer, not sure you had opportunity to read my previous post :

please find an exemple following this link

BR

Frederic 

https://drive.google.com/drive/folders/1LPEXdEazP3qwA9dm3Vo8OF1Zx_QqSZE2