none
Generate a "flattened" table from another table with a nested list as a field. RRS feed

  • Question

  • If I have a table that I already imported into my data model and it has data something along the lines of (greatly simplified):

    Customer Query_Count_Date First View Date Last View Date Category Site
    A 1|0|3|0|4 7/25/2015 7/29/2015 198 www.abdc.com
    B 1|0|5 7/1/2015 7/3/2015 197 www.blah.net

    Is there a way to use an M script or Power Query to turn this into a flattened table with a row for each date beginning with the First View Date with +1 row for each Query_Count for the dates until the Last View Date?  The table would look like the following:

    Customer Query_Count Date Category Site
    A 1 7/25/2015 198 www.abdc.com
    A 0 7/26/2015 198 www.abdc.com
    A 3 7/27/2015 198 www.abdc.com
    A 0 7/28/2015 198 www.abdc.com
    A 4 7/29/2015 198 www.abdc.com
    B 1 7/1/2015 107 www.blah.net
    B 0 7/2/2015 197 www.blah.net
    B 5 7/3/2015 197 www.blah.net


    Any assistance with how I could use the top table to generate the bottom flattened version would be incredibly helpful (the top table is coming from a CSV file imported into the data model).  Thank you




    • Edited by LeaningTree Thursday, July 30, 2015 1:20 AM
    Thursday, July 30, 2015 1:12 AM

Answers

  • And another method.


    let Source = Csv.Document(File.Contents("C:\CSV\SampleData.csv"),[Delimiter=",",Encoding=1252]), PromoteHeaders = Table.PromoteHeaders(Source), ChType = Table.TransformColumnTypes( PromoteHeaders, {{"Customer", type text}, {"Query_Count_Date", type text}, {"First View Date", type date}, {"Last View Date", type date}, {"Category", Int64.Type}, {"Site", type text}} ), AddCol = Table.AddColumn(ChType, "QCD", each Table.FromColumns( {Text.Split([Query_Count_Date], "|"), {Number.From([First View Date])..Number.From([Last View Date])}} , {"Query_Count_Date", "Date"}

    ) ), RemCol = Table.SelectColumns(AddCol,{"Customer", "QCD", "Site"}), ExpCol = Table.ExpandTableColumn(

    RemCol, "QCD", {"Query_Count_Date", "Date"}, {"Query_Count_Date", "Date"} ), ChType2 = Table.TransformColumnTypes(

    ExpCol, {{"Query_Count_Date", Int64.Type}, {"Date", type date}} ) in ChType2

    Regards


    • Edited by Bill Szysz Thursday, July 30, 2015 9:24 AM
    • Marked as answer by LeaningTree Friday, July 31, 2015 1:11 AM
    Thursday, July 30, 2015 9:23 AM

All replies

  • Well, in M you could split the Query_Count_Date. the DAX DATESBETWEEN function might help, that's a really nasty issue. Hang on, this should get you closer:

    let
        Source = Csv.Document(File.Contents("C:\Temp\hits.csv"),[Delimiter=",",Encoding=1252]),
        #"Promoted Headers" = Table.PromoteHeaders(Source),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer", type text}, {"Query_Count_Date", type text}, {"First View Date", type date}, {"Last View Date", type date}, {"Category", Int64.Type}, {"Site ", type text}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Query_Count_Date",Splitter.SplitTextByDelimiter("|"),{"Query_Count_Date.1", "Query_Count_Date.2", "Query_Count_Date.3", "Query_Count_Date.4", "Query_Count_Date.5"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Query_Count_Date.1", Int64.Type}, {"Query_Count_Date.2", Int64.Type}, {"Query_Count_Date.3", Int64.Type}, {"Query_Count_Date.4", type text}, {"Query_Count_Date.5", type text}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Customer", "First View Date", "Last View Date", "Category", "Site "}, "Attribute", "Value")
    in
        #"Unpivoted Columns"

    You can probably get there from that code. Will give you this:

    Customer First View Date Last View Date Category Site Attribute Value
    A 7/25/2015 12:00:00 AM 7/29/2015 12:00:00 AM 198 www.abdc.com Query_Count_Date.1 1
    A 7/25/2015 12:00:00 AM 7/29/2015 12:00:00 AM 198 www.abdc.com Query_Count_Date.2 0
    A 7/25/2015 12:00:00 AM 7/29/2015 12:00:00 AM 198 www.abdc.com Query_Count_Date.3 3
    A 7/25/2015 12:00:00 AM 7/29/2015 12:00:00 AM 198 www.abdc.com Query_Count_Date.4 0
    A 7/25/2015 12:00:00 AM 7/29/2015 12:00:00 AM 198 www.abdc.com Query_Count_Date.5 4
    B 7/1/2015 12:00:00 AM 7/3/2015 12:00:00 AM 197 www.blah Query_Count_Date.1 1
    B 7/1/2015 12:00:00 AM 7/3/2015 12:00:00 AM 197 www.blah Query_Count_Date.2 0
    B 7/1/2015 12:00:00 AM 7/3/2015 12:00:00 AM 197 www.blah Query_Count_Date.3 5

    Thursday, July 30, 2015 1:57 AM
  • You can do the entire transformation through the Power query UI. The code generated (which I formatted a bit) is:

    let
        Source = Csv.Document(File.Contents("C:\Temp\hits.csv"),[Delimiter=",",Encoding=1252]),
        #"Promoted Headers" = Table.PromoteHeaders(Source),
        #"Changed Type" = Table.TransformColumnTypes(
                                                     #"Promoted Headers",
                                                     {{"Customer", type text},
                                                     {"Query_Count_Date", type text},
                                                     {"First View Date", type date},
                                                     {"Last View Date", type date},
                                                     {"Category", Int64.Type},
                                                     {"Site", type text}}
                                                     ),
        #"Split Column by Delimiter" = Table.SplitColumn(
                                                         #"Changed Type",
                                                         "Query_Count_Date",
                                                         Splitter.SplitTextByDelimiter("|"),
                                                         {"Query_Count_Date.1",
                                                         "Query_Count_Date.2",
                                                         "Query_Count_Date.3",
                                                         "Query_Count_Date.4",
                                                         "Query_Count_Date.5"}
                                                         ),
        #"Changed Type1" = Table.TransformColumnTypes(
                                                      #"Split Column by Delimiter",
                                                      {{"Query_Count_Date.1", Int64.Type},
                                                      {"Query_Count_Date.2", Int64.Type},
                                                      {"Query_Count_Date.3", Int64.Type},
                                                      {"Query_Count_Date.4", type text},
                                                      {"Query_Count_Date.5", type text}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(
                                                         #"Changed Type1",
                                                         {"Customer",
                                                         "First View Date",
                                                         "Last View Date",
                                                         "Category", "Site"},
                                                         "Attribute", "Value"),
        #"Changed Type2" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", Int64.Type}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Value", "Query_Count"}}),
        #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Date", each [First View Date]+
                                           #duration(Number.FromText(Text.End([Attribute],1))-1,0,0,0)),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"First View Date","Last View Date", "Attribute"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Customer", "Query_Count", "Date", "Category", "Site"})
    in
        #"Reordered Columns"

    This code is essentially an extension of Seth's code.

    Thursday, July 30, 2015 2:57 AM
  • And another method.


    let Source = Csv.Document(File.Contents("C:\CSV\SampleData.csv"),[Delimiter=",",Encoding=1252]), PromoteHeaders = Table.PromoteHeaders(Source), ChType = Table.TransformColumnTypes( PromoteHeaders, {{"Customer", type text}, {"Query_Count_Date", type text}, {"First View Date", type date}, {"Last View Date", type date}, {"Category", Int64.Type}, {"Site", type text}} ), AddCol = Table.AddColumn(ChType, "QCD", each Table.FromColumns( {Text.Split([Query_Count_Date], "|"), {Number.From([First View Date])..Number.From([Last View Date])}} , {"Query_Count_Date", "Date"}

    ) ), RemCol = Table.SelectColumns(AddCol,{"Customer", "QCD", "Site"}), ExpCol = Table.ExpandTableColumn(

    RemCol, "QCD", {"Query_Count_Date", "Date"}, {"Query_Count_Date", "Date"} ), ChType2 = Table.TransformColumnTypes(

    ExpCol, {{"Query_Count_Date", Int64.Type}, {"Date", type date}} ) in ChType2

    Regards


    • Edited by Bill Szysz Thursday, July 30, 2015 9:24 AM
    • Marked as answer by LeaningTree Friday, July 31, 2015 1:11 AM
    Thursday, July 30, 2015 9:23 AM
  • Thank you all very much for the amazingly helpful and quick replies, great community here.  I am quickly becoming a believer in the Power BI suite, I just have a lot of learning to do for DAX and M code.  I ended up using Bill's answer as it readily allowed me to accommodate some very 30+ days of counts in some of the view "lists."

    Perfect solution, I had switched the source to a table, and it worked perfectly when I took out the PromoteHeaders portion and replaced ChType with Source, thanks again, this really convinced me to work on learning M. 

     
    Friday, July 31, 2015 1:29 AM