Lookup table with comma separated values

[SachinC]

I have a master table with values. My master table has a TagId column with values such as TagId: 123,321,344,455 and a combination of one value, two values, four values etc... (all comma separated).
These numbers correspond to campaign names and these are in a separate table, i.e. 123 This is a test, 321 Hello, 344 This is another test etc.... There are around 40-odd lines in my lookup table with columns: Id (autonumber), ID, TagId, CampaignName
Can you tell me how I can get my Campaign names in place from TagId as I will need to report on the data in PowerBI using Access DB as my datasource?
Do I create a new Query...???
Thanks.

 

[Micron]

Have been dabbling in Access for 30 years and only once in that time did I ever have a need to keep csv data in a table. Unless you have a good reason for doing that, you probably should properly normalize your data. Otherwise, the only way I can see getting your names out is to use code that breaks your csv values into an array but not sure what you'd do with them at that point. Possibly create records in a temp table or a staging table (along with the CampaignName field), because a query that calls a function can only accept one return value per record, and you have several values in one field. You'd use that temp/staging table in a query that joins that table to your names table on CampaignNames fields. Perhaps this could also be done by using a temporary TableDef, but while that's slicker, it's more complicated.

Having said all that, some PowerBI guru will probably whip up something for you 10 minutes after reading these posts.

 

[welshgasman]

I would also say just normalize your table, then you would not have an issue.

 

[alansidman]

With this layout, in Power BI, you can use Power Query

Book6
	A	B	C	D	E	F	G	H
1	Source Data		Output				LOOK UP
2
3	Column1		Column1	Equiv			Code	Equiv
4	123,456,789,012		123	A			123	A
5	234,456,891,123		456	B			456	B
6			456	B			789	C
7			789	C			012	D
8			012	D			234	E
9			234	E			891	F
10			891	F
11			123	A
Sheet2

let
    T1 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    CT = Table.TransformColumnTypes(T1,{{"Column1", type text}}),
    SCD = Table.ExpandListColumn(Table.TransformColumns(CT, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    T2=Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    CT2 = Table.TransformColumnTypes(T2,{{"Code", type text}, {"Equiv", type text}}),
    MQ = Table.NestedJoin(SCD, {"Column1"}, CT2, {"Code"}, "Table2", JoinKind.FullOuter),
    ET = Table.ExpandTableColumn(MQ, "Table2", {"Equiv"}, {"Equiv"})

in
    ET

 

[SachinC]

With this layout, in Power BI, you can use Power Query

Book6
	A	B	C	D	E	F	G	H
1	Source Data		Output				LOOK UP
2
3	Column1		Column1	Equiv			Code	Equiv
4	123,456,789,012		123	A			123	A
5	234,456,891,123		456	B			456	B
6			456	B			789	C
7			789	C			012	D
8			012	D			234	E
9			234	E			891	F
10			891	F
11			123	A
Sheet2

let
    T1 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    CT = Table.TransformColumnTypes(T1,{{"Column1", type text}}),
    SCD = Table.ExpandListColumn(Table.TransformColumns(CT, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    T2=Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    CT2 = Table.TransformColumnTypes(T2,{{"Code", type text}, {"Equiv", type text}}),
    MQ = Table.NestedJoin(SCD, {"Column1"}, CT2, {"Code"}, "Table2", JoinKind.FullOuter),
    ET = Table.ExpandTableColumn(MQ, "Table2", {"Equiv"}, {"Equiv"})

in
    ET

This is fantastic. Exactly what I need. I am actually using MS Access. I have a master table (with a column called TAGId) and lookup table with TagId and Description, I need the TagId resolved to Description. Look forward to your response. Thanks.

 

[alansidman]

Bring your tables into Power Query and run the Mcode. Modify the code to reflect the table and field names of your tables and/or queries. This google lookup will help you to bring that data in

Power query get MS Access tables - Google Search

What version of Excel are you running? In O365, power query is called Get and Transform Data and found on the Data Ribbon. Suggest you update your profile to indicate your Excel/Access versions so that responses can be catered to your particular version.