Forum Discussion

IF's avatar
IF
Post Prodigy
5 years ago
Solved

computed entity

Hi, I am getting data from a dataflow for my dataset. The dataflow that I receive data is huge, millions of records. It really slows down my report if I get it all. I wonder if it is possible to com...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    5 years ago

    Hi IF ,

    After you create a data stream and connect to the data source, transform the table in ‘Edit Query’, please refer to the following code.

    let

        Source = Excel.Workbook(File.Contents("C:\Users\lionelch\Desktop\Sample data\1.xlsx"), null, true),

        Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],

        #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),

       #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Count", type text}, {"Date", type number}, {"type", type text}, {"value", Int64.Type}}),

        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.RowCount(#"Changed Type")),

        #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"value", Int64.Type}}),

        #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "__Custom"}}),

        #"Grouped Rows" = Table.Group(#"Renamed Columns", {"__Custom", "Date", "type"}, {{"__value", each List.Sum([value]), type nullable number}})

    in

        #"Grouped Rows"

     

     

     

v-xuding-msft's avatar
v-xuding-msft
Community Support
5 years ago

Hi IF ,

After you create a data stream and connect to the data source, transform the table in ‘Edit Query’, please refer to the following code.

let

    Source = Excel.Workbook(File.Contents("C:\Users\lionelch\Desktop\Sample data\1.xlsx"), null, true),

    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),

   #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Count", type text}, {"Date", type number}, {"type", type text}, {"value", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.RowCount(#"Changed Type")),

    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"value", Int64.Type}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "__Custom"}}),

    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"__Custom", "Date", "type"}, {{"__value", each List.Sum([value]), type nullable number}})

in

    #"Grouped Rows"