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

[Anonymous]

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: 

Cost	ID A	ID B	ID C
100	a1as	null	null
200	null	csds	null
300	null	null	sdsdsds

TABLE B

ALIAS	ID TYPE	ID VALUE
123	ID A	a1as
423	ID B	CSDS

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:

ALIAS	COST
123	100
423	200

I'm just not seeing it right.

[v-jingzhang · Accepted solution]

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.

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

[Anonymous]

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]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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]

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"

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]

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