Forum Discussion
Helper IUnifiying ever increasing columm numbers to a single data source
Use this for your Unpivot step
Table.Unpivot(#"Removed Columns", List.RemoveNulls(List.Transform(Table.ColumnNames(#"Removed Columns"),each try if List.Contains(List.Numbers(1,12),Number.FromText(Text.Split(_,"-"){0})) then _ else null otherwise null)), "Attribute", "Value")
Helper IHello Vijay
-Yes all columms prior to the 1-2022 2-2022 .... are fixed and identical
-Yes all colummss labeled 1-2022, 2-2022 will need to be unpivoted so they can be turned into row data
thanks
Most Valuable ProfessionalUse this for your Unpivot step
Table.Unpivot(#"Removed Columns", List.RemoveNulls(List.Transform(Table.ColumnNames(#"Removed Columns"),each try if List.Contains(List.Numbers(1,12),Number.FromText(Text.Split(_,"-"){0})) then _ else null otherwise null)), "Attribute", "Value")
- Emrecan
Hello Vijay
for reading the last file on the folder when clicked on the binary
a referenace is created to the file name. So if a new file is draged or dropped in the folder the querry fails
for the unpivot action. The error persist and is generated from the columm number
thanks
- Vijay_A_Verma
Delete this step from your query. This is not needed.
- Emrecan
Hello Vijay
Yes it works now. However reading from the last file on the folder wasnt part of my original querry. How can we combine both?
thanks
- Vijay_A_Verma
I want to know if you know the concept of importing from folder and how to manipulate Transform file. Accordingly, I will draft my response.
- Emrecan
Hello Vijay
I am at best novice in power bi and no experience with M
thanks
- Emrecan
Hello Vijay
Looking at the unpivot code I was trying to understandt it to use it for other instances.
what is _ in the code here (Text.Split(_,"-"){0})). From what I can gather it means split the current with delimeter "-" and take the first columm?. I couldnt find this kind of usage of text split in documantation.
thanks
- Vijay_A_Verma
_ means current which you have rightly mentioned.
Text.Split(_,"-") will split 1-2022 into two parts - 1 and 2022 > {0} will pickup 1st element which is 1 here. In next round, it will be applied on 2-2022 and so on.
This is little bit advanced but as you move on in your PQ journey, you will understand and implement these.