Multiple IDs in one row

[SarahAlsterspre]

Hey,

I have the following problem. I have 3 tables and I would like to create a third one. The tricky part is that multiple IDs can be in one row. I have the following tables:

table 1 includes all the data (incl. examples)

URL	Clicks	Last Click (Date)
www.._KG0034_AU0023	2345	10.10.2021
www...._KG0003_AU0004_AU0012	1234	12.10.2021

table 2 is an indextable

customer (AU)	Name
AU0001	nosdljf
AU0002	nfksfjd

table 3 also is an indextable

Event (KG)	name
KG0001	nösfd
KG0002	ijjflskd

What I need is a table that extracts all the clicks for each customer and event (if stated):

customer (AU)	Clicks	Date	Event (KG)
AU0023	2345	10.10.2021	KG0034
AU0004	1234	12.10.2021	KG0003
AU0012	1234	12.10.2021	KG0003
...	...	...	...

I hope that I could clearly state my problem and am looking forward to any suggestions

[SarahAlsterspre]

thank you very much. this helped and solved the problem

[Dinesh_Suranga · Accepted solution]

@SarahAlsterspre 

You have to clean your table1.

Use following M code to clean your table1 in power query side.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi8v19OL93Y3MDA2iXcMNTAwMlbSUTIyNjEFUoYGekBkZGBkqBSrA1ULVW1gDFZtANFkaARSDdQFphCaYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [URL = _t, Clicks = _t, #"Last Click (Date)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"URL", type text}, {"Clicks", Int64.Type}, {"Last Click (Date)", type date}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Clicks", "Last Click (Date)", "URL"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "URL", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"URL.1", "URL.2", "URL.3", "URL.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"URL.1", type text}, {"URL.2", type text}, {"URL.3", type text}, {"URL.4", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"URL.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"URL.2", "Event KG"}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Clicks", "Last Click (Date)", "Event KG"}, "Attribute", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Value", "Customer"}})
in
#"Renamed Columns1"

Then you can get your table1 as following.

Then go to visual view and add columns to table visual.

Thank you.