Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Thrasymaque
Frequent Visitor

how to combine lookupvalue on multiple columns ?

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.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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

lbendlin_0-1614350215363.png

 

 

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

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

lbendlin_0-1614350215363.png

 

 

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
Frequent Visitor

Hello 

 

please find an exemple following this link

BR

Frederic 

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

lbendlin
Super User
Super User

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.

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors