Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Merge data of same column names into same column

I have several Excel files that will be generated weekly and produce different weekly forecast. The number of week forecasts varies for each file. Below are the examples:

Week 1:

 

 

Week 2:

 

Week 3:

 

 

I wish to transform the dataset to below image in Power Bi Power query:

 

-The new weeks generated for each file will be added to the right of previous file.

-The categories 'Condition' and 'Region' are sorted into same columns.

-In the future, the newly generate files (File 4, File 5 and so on) can be combined automatically into subsequent rows through the query.

Basically, I wish to compare the forecast difference on the same date of different files for each category. 

Any idea to do this? I am new in Power Bi. Really appreaciate any help given. 

Thank you.

 

 

 

Need Help

13 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Anonymous ,

     

    Providing all of the columns in each file have the same data type, then you should just be able to append all the tables in Power Query.

    The most dynamic way to do this would be to keep all of your forecast files in one folder together, then use either the Folder connector, or the SharePoint folder connector to pick up all files in the folder and append them at refresh.

     

    https://docs.microsoft.com/en-us/power-query/connectors/folder 

    https://docs.microsoft.com/en-us/power-query/connectors/sharepointfolder 

     

    Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      yes, however, the columns of subsequent files are not sorted to the same column as their positions are different. 

      • BA_Pete's avatar
        BA_Pete
        Super User
        4 years ago

        Hi Anonymous ,

         

        Have you tried it?

        As far as I'm aware, Power Query append works differently to SQL UNION in that it matches columns to append based on the column names, not on the order in which they feature.

         

        *EDIT*

         

        I've just tested, and given the following source tables:

        // aTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIFYjOlWB0IzwKIzcG8JJC4JVgIxjUBYiOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cat = _t, #"01/01/2022" = _t, #"01/02/2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"cat", type text}, {"01/01/2022", Int64.Type}, {"01/02/2022", Int64.Type}})
in
    #"Changed Type"

// bTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIFYjOlWB0IzwKIzcG8ZJC4JVgIxjUBYiOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cat = _t, #"01/02/2022" = _t, #"01/03/2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"01/02/2022", Int64.Type}, {"01/03/2022", Int64.Type}})
in
    #"Changed Type"

// cTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIFYjOlWB0IzwKIzcG8JJC4JVgIxE0GskyA2EgpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cat = _t, #"01/04/2022" = _t, #"01/05/2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"01/04/2022", Int64.Type}, {"01/05/2022", Int64.Type}})
in
    #"Changed Type"

         

        And appending as follows:

        let
    Source = Table.Combine({aTable, bTable, cTable})
in
    Source

         

        I get the following output, which looks like what you want, right?

         

        Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      pardon, the link redirects me to a blank onedrive excel file 

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        Looks like you missed reading this - You can see the queries from Data tab - Queries and connections

        You can double click on Test

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      It shows error after the week 2 and week 3 files are added into the folder 

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Impactful Individual
    4 years ago

    Hi Sunshiner,

     

    The issues come from each file's column names being column1,2,3,.........

    Promoteheader need to be done as part of the transformation.

     

    Below is the code:

    let
    //Import from folder
    Source = Folder.Files("C:\Users\cktan\Documents\PQ Solutions"),

    //Filtered out hidden files
    #"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "~")),

    //Get table/data from each workbook
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "GetTbl", each Excel.Workbook([Content])),
    #"Expanded GetTbl1" = Table.ExpandTableColumn(#"Added Custom", "GetTbl", {"Data"}, {"Data"}),

    //Promote Headers before expand
    PromoteHdrs = Table.AddColumn(#"Expanded GetTbl1", "PromoteHdrs", each Table.PromoteHeaders([Data], [PromoteAllScalars=true])),

    //Get Column Names from each workbook
    GetColNames = Table.AddColumn(PromoteHdrs, "GetColumnNames", each Table.ColumnNames([PromoteHdrs])),

    //Remove duplication of ColumnNames
    ColumnNamesList = List.Distinct(Table.SelectColumns(Table.ExpandListColumn(GetColNames, "GetColumnNames"),"GetColumnNames")[GetColumnNames]),

    //Removed other columns before expand
    #"Removed Other Columns" = Table.SelectColumns(GetColNames,{"PromoteHdrs"}),

    //Dynamic Column Name list for expanding
    #"Expanded PromoteHdrs" = Table.ExpandTableColumn(#"Removed Other Columns", "PromoteHdrs", ColumnNamesList)
    in
    #"Expanded PromoteHdrs"

     

     

     

    I hope this help.

     

    Regards

    KT