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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Create a x-ref table based on 1 of several columns of a table

I need some help on how to do this. I have one table that is storing an identifier or multiple identifier. My warehouse table is storing a value called alias on it's table but the alias is attributed by a column called ID VALUE.

 

For example.

 

Table A: 

CostID AID BID C
100a1asnullnull
200nullcsdsnull
300nullnullsdsdsds

 

 

TABLE B

ALIASID TYPEID VALUE
123ID Aa1as
423ID BCSDS

 

 

 

How can I make a table that merges the able to give just the ALIAS and the COST depending on the value in TABLE B?

 

So the table should look like:

ALIASCOST 
123100 
423200 

 

I'm just not seeing it right.

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

I have an easier method to achieve your goal. Please refer to below steps. Here is a PBIX file for your reference.

  1. In Table A, select ID A... ID C columns and click Unpivot Columns. Or select Cost column and click Unpivot Other Columns. 
  2. Filter out null values in Values column.
  3. In Table B, click Merge Queries or Merge Queries as New. Merge Table A to Table B based on column Table B [ID TYPE] and Table A [Attribute] with Left Outer Join Kind.
  4. Expand the merged table column and select the columns you need. After that, remove the columns you don't need and you will get what you want.

022406.jpg

022407.jpg

Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

View solution in original post

6 REPLIES 6
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

I have an easier method to achieve your goal. Please refer to below steps. Here is a PBIX file for your reference.

  1. In Table A, select ID A... ID C columns and click Unpivot Columns. Or select Cost column and click Unpivot Other Columns. 
  2. Filter out null values in Values column.
  3. In Table B, click Merge Queries or Merge Queries as New. Merge Table A to Table B based on column Table B [ID TYPE] and Table A [Attribute] with Left Outer Join Kind.
  4. Expand the merged table column and select the columns you need. After that, remove the columns you don't need and you will get what you want.

022406.jpg

022407.jpg

Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

Anonymous
Not applicable

I like this solution. This is what I wanted to do but couldn't figure it out because of "newbie" ness. Thanks. Actually, thank you all. All of these are the right solutions just different ways! thanks everyone

 

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish,

 

That may work for my small example. You most definitely want to do the join there but you more likely will get many more pieces of information.

 

The real world example is from a Data Warehouse

 

We get prices and security data from many sources of information. The only way to apply meta data is to columns on the data and then tag the data.

 

I think the best is to pivot the table but I'm not sure how to do that in PowerBI. There is a solution below that I am going to try but maybe you had a thought. Thank you for your response, however.

 

AlB
Super User
Super User

Hi @Anonymous 

Place the following M code in a blank query to see the steps. See it all at work in the attached file.

let
    Source = TableA,
    #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"ID A","ID B","ID C"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Filled Up" = Table.FillUp(#"Transposed Table",{"Column1", "Column2", "Column3"}),
    #"Kept First Rows" = Table.FirstN(#"Filled Up",2),
    #"Transposed Table1" = Table.Transpose(#"Kept First Rows"),
    #"Renamed Columns" = Table.RenameColumns(#"Transposed Table1",{{"Column1", "Cost"}, {"Column2", "ID VALUE"}}),
    #"Merged Queries" = Table.NestedJoin(TableB, {"ID VALUE"}, #"Renamed Columns", {"ID VALUE"}, "ResultT", JoinKind.LeftOuter),
    #"Expanded ResultT" = Table.ExpandTableColumn(#"Merged Queries", "ResultT", {"Cost"}, {"Cost"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded ResultT",{"ID TYPE", "ID VALUE"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Cost", type number}})
in
    #"Changed Type"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Anonymous
Not applicable

But to use in my pbi file, I would have to use this a series of data ETL steps, correct?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors