Forum Discussion

SarahAlsterspre's avatar
SarahAlsterspre
Helper II
3 years ago
Solved

Multiple IDs in one row

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)

URLClicksLast Click (Date)
www.._KG0034_AU0023234510.10.2021
www...._KG0003_AU0004_AU0012123412.10.2021

 

table 2 is an indextable

customer (AU)Name
AU0001nosdljf
AU0002

nfksfjd

 

table 3 also is an indextable

Event (KG)name
KG0001

nösfd

KG0002ijjflskd

 

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

 

customer (AU)ClicksDateEvent (KG)
AU0023234510.10.2021KG0034
AU0004123412.10.2021KG0003
AU0012123412.10.2021KG0003
............
    

 

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

Need Help
  • Dinesh_Suranga's avatar
    Dinesh_Suranga
    3 years ago

    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.

     

     

2 Replies

  • Dinesh_Suranga's avatar
    Dinesh_Suranga
    Continued Contributor
    3 years ago

    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.