Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi all..i am trying to pivot column"merged" but got error.
can anyone advice where i have gone wrong...
Thank you so much.
Solved! Go to Solution.
Hi,
See if my solution here helps - Append data from multiple worksheets of multiple workbooks where each worksheet has a different head....
It's a bit difficult to debug because even though you have included the pbix, we don't have access to the source spreadsheet.
You could post the spreadsheet and tell us what your desired outcome is..
Having said that, the most common pivot error is "There were too many elements in the enumeration to complete the operation".
Is that what you have?
If so, it is usually because there's no aggregation on the pivot. So powerbi doesn't know how to cope with rows that have a similar 'key'.
@HotChilli
Here is the excel and the output below.
As the file was also used among the team, so i was wondering if changes can be done within power bi itself or minimum change to excel.
Thank you for the advice.
Hi,
See if my solution here helps - Append data from multiple worksheets of multiple workbooks where each worksheet has a different head....
Hi,
My method should work. Please retry. I am travelling and therefore cannot try my method on your file right now.
The second file is not in the same format as the one you posted earlier.
The second one has a Merge column which contains the data from 2 columns in the first file, so that's why it's not working
I imported each sheet as a different query-> then transformed each sheet similarly ->
I appended the 3 queries together to get the final formatted data
Here's the advanced editor code for Sheet1.
let
Source = Excel.Workbook(File.Contents("J:\data\powerbiForum\zshop.xls"), null, true),
Sheet2 = Source{[Name="Sheet1"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([City] = "New York" or [City] = "Ohio")),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"City", "Product ID"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"
So if you create 3 queries and copy this code for each, you'll need to alter the line "Source{[Name="SheetX.......
replacing X with the Sheet number.
Once you've done that 'Append Queries as new' and tidy up the column names and data types. Like this
et
Source = Table.Combine({Sheet1, Sheet2, Sheet3}),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Attribute", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}, {"Date", type date}})
in
#"Changed Type"
just curious, if new a data sheet is added to this excel, say 2020 data, will it get refreshed?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |