Unpivot Question

[SnowBoardTim]

Hello all!  I need some help in making a table friendlier to manipulate in Power BI and use their file as the source so I can capture their updates.  Above is a picture of what I am working on.  It is a huge file that extends to the right quite a bit.  Basically what you have is...

• First 2 columns are not repeated.  
• The remaning 10000 columns are repeated every 18 columns.  If you look above at the Orange fill you will see Actual W Forecast.  THis category has the below and than it will be repeated with an example as "Last Year" with last year numbers.  
  • Period 1-13 to track values for each month
  • QTR1-4 with a total column.  

What I am trying to do is have a column of those "categories" such as Actual and Budgets that I can filter VS scrolling to the right to see everything.  I am not able to manipulate the data into an easier visual the way this is built and it is a large file.  In watching some videos, I was able to partially get the data like I wanted by turning into lists and manipulating into tables and bringing in the column headers from there.   The problem I have is the first three columns.  When I create a list with a page break of say 18 (13 periods & 5 QTR and Total columns) the first three throw that first list off and the subsequent lists.  Basically I assume I need to remove the three columns before I start manipulating the tables further and than bring in the columns at the end.  I did that through a merge with a copy of the table, but I know there is a better way.  Below is a screenshot of the column I was able to create with the categories.  

Thanks all.  

[v-pagayam-msft]

Hi @SnowBoardTim ,
Could you please confirm if the issue has been resolved on your end? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.If our answer found helpful, consider accepting it as solution.
Thank you for your understanding and assistance.

[v-pagayam-msft]

Hi @SnowBoardTim ,
Thank you for the patience! If the issue is still not resolve,please refer the answer below:

There is a limitation when importing merged columns to Power BI , the first column will reflect as is, while the other column will appear blank in the header. To avoid this issue, unmerge the columns in the header and copy the same header to all the columns manually. Then, the rows can be merged using the m code below. Please change the source as per your requirements. Attached the pbix file for reference.

let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("i45WCs4sSVXSUXJMTs4vzSsBsgJSizLzUwzhLCM4yxjOMoGzTOEsMzjLXClWJ1rJxxEoEAHEfvqORJEgTb6pJUX5Bfk5mcWkao4FAA==", BinaryEncoding.Base64),
Compression.Deflate
)
),
let _t = ((type nullable text) meta [Serialized.Text = true]) in 
type table [#"(blank)" = _t, #"(blank).1" = _t, #"2024 Actual W Forecast" = _t, #"2024 Actual W Forecast.1" = _t, #"2024 Actual W Forecast.2" = _t, #"2024 Actual W Forecast.3" = _t, #"2024 Actual W Forecast.4" = _t, #"2024 Actual W Forecast.5" = _t, #"2024 Actual W Forecast.6" = _t]
),

OriginalHeaders = Table.ColumnNames(Source),

//  Cleaned headers: remove .1/.2 suffixes and replace (blank) with space
CleanedHeaders = List.Transform(
OriginalHeaders, 
each Text.Replace(Text.BeforeDelimiter(_, "."), "(blank)", "")
),

FirstRow = Source{0},
FirstRowValues = Record.ToList(FirstRow),

// Combine cleaned header + first row value
CombinedHeaders = List.Transform(
List.Zip({CleanedHeaders, FirstRowValues}),
each Text.Combine(List.RemoveNulls(_), " ")
),

DataWithoutFirstRow = Table.Skip(Source, 1),
FinalTable = Table.RenameColumns(DataWithoutFirstRow, List.Zip({Table.ColumnNames(DataWithoutFirstRow), CombinedHeaders}))
in
FinalTable

If this answer meets your requiremnets, consider accepting it as solution.

Thank you.

[v-pagayam-msft]

Hi @SnowBoardTim ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-pagayam-msft]

Hi @SnowBoardTim  ,

I just wanted to kindly follow up to see if you had a chance to review my previous response. Please let me know if it was helpful, and feel free to reach out if needed any further assistance. If our community member's repsonse addressed your query, please Accept the answer so that it will be helpful to others to find it more quickly.
Thank you for being a part of Microsoft Fabric Community Forum!

[SnowBoardTim]

I was not able to finalize the solution.  I replied to your previous comments.  Thanks for the help!

[v-pagayam-msft]

Hi @SnowBoardTim ,
Thank you for reaching out to the Microsoft Fabric Community Forum regarding the issue you're facing.

I attempted to recreate the scenario locally using sample data to help you manage the large dataset. Below are the steps that might assist you in handling the data:

1.Once loading data into Power BI, go to power query editor.
2.Except first two rows, select all the repeated columns, Right-click and select Unpivot Column.
3. After that, Will get Attribute and Value columns. Split Attribute column into multiple columns using a delimiter. Rename new columns with the required column name.
4.Now, merge the original first two columns back with the transformed data as the first 2 columns were separated during the unpivoting process.

I have also provided a sample output for your reference. If you still require further assistance, please feel free to share the sample data for more precise help.

[v-pagayam-msft]

Hi @SnowBoardTim ,
Thank you for the response.

I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you for being a part of Microsoft fabric Community Forum!
Regards,
Pallavi.

[SnowBoardTim]

It was close.  THere is a row above that I need brought down to rows.  Once I figure out best way to attach the Power BI you sent, I will send.  I added two tables to it.  One is how it looks when I pull in the data and one is how I want it to look. 

Thanks for help  

[SnowBoardTim]

Here is a lik to the pbi I edited to add my tables.  

https://drive.google.com/file/d/1xEyREtr3y_HCNcS55ZojvO8xOudKMwZH/view?usp=drive_link

Thanks again for help

[pcoley]

@SnowBoardTim 
Please try with this solution, renaming steps and headers as desired:

let
    Source = Excel.Workbook(
        File.Contents("D:\...your path...\filename.xlsx"), null, true
    ),
    Nav_Sheet = Source{[Item = "...your sheetname...", Kind = "Sheet"]}[Data],
/// before this line must be your original code, please check stepnames at the query.

    TransposeTable = Table.Transpose(Nav_Sheet),
    Filldown = Table.FillDown(TransposeTable, {"Column1"}),
    CombColumns = Table.CombineColumns(
        Filldown, {"Column1", "Column2"}, Combiner.CombineTextByDelimiter("-", QuoteStyle.None), "Combinada"
    ),
    ReverseTranspose = Table.Transpose(CombColumns),
    Header = Table.PromoteHeaders(ReverseTranspose, [PromoteAllScalars = true]),
    Unpivot = Table.UnpivotOtherColumns(Header, {"-site", "-account"}, "Attribut", "Value"),
    Columns = Table.SplitColumn(
        Unpivot, "Attribut", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribut.1", "Attribut.2"}
    ),
    Type = Table.TransformColumnTypes(Columns, {{"Attribut.1", type text}, {"Attribut.2", type text}})
in
    Type

 Hope this help, if so please accept the solution. Kudos are welcome😀.

[SnowBoardTim]

I pulled in the code from the source and the navigation step and changed in your code, but it gives me the "expecting a token" error.  Did I adjust wrong?  BTW you inspire me to learn M.  I highlighted in orange what I changed.  

let

= Excel.Workbook(File.Contents("My Location"), null, true)    ),

    Source{[Item="File Name",Kind="Sheet"]}[Data],

    TransposeTable = Table.Transpose(Nav_Sheet),

    Filldown = Table.FillDown(TransposeTable, {"Column1"}),

    CombColumns = Table.CombineColumns(

        Filldown, {"Column1", "Column2"}, Combiner.CombineTextByDelimiter("-", QuoteStyle.None), "Combinada"

    ),

    ReverseTranspose = Table.Transpose(CombColumns),

    Header = Table.PromoteHeaders(ReverseTranspose, [PromoteAllScalars = true]),

    Unpivot = Table.UnpivotOtherColumns(Header, {"-site", "-account"}, "Attribut", "Value"),

    Columns = Table.SplitColumn(

        Unpivot, "Attribut", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribut.1", "Attribut.2"}

    ),

    Type = Table.TransformColumnTypes(Columns, {{"Attribut.1", type text}, {"Attribut.2", type text}})

in

    Type

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...